<?php

/**
 * Created by PhpStorm.
 * User: daimin
 * Date: 15/7/22
 * Time: 下午6:31
 */

namespace common\services\common;

use common\entities\ConfigEntity;
use common\entities\KfsDbEntity;
use common\entities\myhtsq\MyscrmParttimeBusinessEntity;
use common\models\ConnectionModel;
use common\repositories\common\MyscrmContractProjectRepository;
use common\repositories\common\MyscrmContractRepository;
use common\repositories\common\MyscrmOperlogRepository;
use common\repositories\common\MyscrmOrganizationRemindRepository;
use common\repositories\common\MyscrmProductquotaRepository;
use common\repositories\common\PigcmsUsersRepository;
use common\repositories\myhtsq\ContractManageRepository;
use common\repositories\myhtsq\ContractReceivableRepository;
use common\repositories\myhtsq\ErpBusinessUnitRepository;
use common\repositories\myhtsq\ErpUserRepository;
use common\repositories\myhtsq\RoleUserRepository;
use common\services\BaseService;
use common\entities\common\MyscrmOrganizationEntity;
use common\repositories\common\MyscrmOrganizationRepository;
use common\services\config\ConfigSettingService;
use common\services\config\MyscrmFunctionupgradeService;
use common\services\myhtsq\ContractService;
use common\services\myhtsq\ErpInterfaceService;
use common\services\myhtsq\ProjectService;
use common\services\outstock\FlowCenterService;
use common\support\Curl;
use common\support\EmailHelper;
use common\support\JWTHelper;
use common\support\LogHelper;
use common\support\OSS;
use common\support\ServiceHelper;
use common\support\StringHelper;
use common\traits\AnchorTrait;
use PDOException;
use Yii;
use yii\base\Exception;
use yii\db\Connection;
use yii\helpers\ArrayHelper;
use common\support\PageParam;

/**
 * 租户操作
 * Class MyscrmOrganizationService
 * @package common\services\common
 */
class MyscrmOrganizationService extends BaseService
{

    use AnchorTrait;

    private static $sheetIndex = 0;
    public $_organizationRepository;

    const DEFAULT_CREATE_ORGANIZATION_DATABASE_INSTANCE_ID = 'ACCESS_KEY_INSTANCEFILTER';
    const EMPTY_CREATEORGANIZATION_TEMPLATE_SQL = '@runtime/empty_database.sql';
    const EMPTY_DATABASE_NAME = 'empty';
    const ASYNC_SERVICE_SCHEDULE_KEY = 'AsyncServiceScheduleKey';
    const EMPTYGUID = '00000000-0000-0000-0000-000000000000';
    const BASE_PRODUCT_CODE = 'SMS';
    const IMMEDIATELY = 1;

    const MARKET_BASE_URL = 'MarketBaseUrl';
    const MARKET_JWT_SECRET = 'MarketJWTSecret';
    const MARKET_JWT_KEY = 'MarketJWTKey';

    const EMPTY_XUNJIAN_TEMPLATE_SQL = '@runtime/empty_xunjian_dump.sql';
    const EMPTY_ORG_CODE = 'empty';
    const FUNCTION_NAME = '巡检报表';//巡检报表
    const INIT_START_DATE = '2014-05-01';//初始化起始时间　2014-05-01
    const EXTRA_INIT_JOB_ID = 'a96ee0da-fd60-11e5-b308-00155d02c832';//额外的初始化任务ID

    const PROJECT_AUTH_ROLE_NAME_400 = '400角色';

    private $defaultXunjianDb;

    //会否有回款信息
    private $hasReceivable=[];

    //缓存:导出中的联系信息
    private $exportContact=[];

    public function __construct(MyscrmOrganizationRepository $organizationRepository)
    {
        $this->_organizationRepository = $organizationRepository;
        parent::__construct();
        $default_xunjian_database_instance_id = ServiceHelper::getConfigByKey('DEFAULT_XUNJIAN_DATABASE_INSTANCE_ID');
        /* @var $databaseInstanceConfigService MyscrmDatabaseInstanceConfigService */
        $databaseInstanceConfigService = Yii::createObject(MyscrmDatabaseInstanceConfigService::className());
        $databaseInstance = $databaseInstanceConfigService->getConfigById($default_xunjian_database_instance_id);

        if (empty($databaseInstance)) {
            throw new Exception("找不到默认的巡检DB");
        }

        $this->defaultXunjianDb = $databaseInstance;
    }

    /**
     * 获取租户列表分页
     * @param string $keyword 搜索关键字
     * @param int $isBq 是否百强租户
     * @param int $isTest 是否测试租户
     * @param int $page 页码
     * @param int $pageSize 每页大小
     * @param string $sortField 排序的字段
     * @param int $order 升序或降序，请使用系统常量
     * @return array
     */
    public function getOrganizationListByPage($keyword, $isBq = 0, $isTest = 0, $page = 1, $pageSize = 20, $sortField = 'CreatedOn', $order = SORT_DESC)
    {
        $pageParam = new PageParam($page, $pageSize, $sortField, $order == SORT_DESC);
        $orgList = $this->_organizationRepository->getOrganizationListByPage($pageParam, $keyword, $isBq, $isTest);

        $orgList = $this->makeTestOrgStatus($orgList);

        return $orgList;
    }

    private function makeTestOrgStatus($orgList)
    {
        $allTestOrgList = $this->_organizationRepository->getAllTestOrg();

        if(empty($allTestOrgList)){
            return $orgList;
        }

        $testOrgCodes = ArrayHelper::getColumn($allTestOrgList, 'orgcode', false);

        foreach ($orgList['items'] as &$org){
            $org['isTest'] = 0;
            if(in_array($org['UniqueName'], $testOrgCodes)){
                $org['isTest'] = 1;
            }
        }

        return $orgList;
    }

    /**
     * 根据租户UniqueName(企业号)获取其ID
     * @param $orgName
     * @return string
     */
    public function getOrgId($orgName)
    {
        return $this->_organizationRepository->getOrgId($orgName);
    }
    /**
     * 根据租户UniqueName(企业号)获取其ID
     * @param $orgName
     * @return array
     */
    public function getAllOrgs($fields="")
    {
        return $this->_organizationRepository->getAllOrgs($fields);
    }

    /**
     * 根据租户ID得到租户信息
     * @param $id
     * @return null|static
     */
    public function getOrgById($id)
    {
        return $this->_organizationRepository->getById($id);
    }
    public function getOrgByUniqueName($orgCode)
    {
        return $this->_organizationRepository->getOrgByUniqueName($orgCode);
    }
    /**
     * 更新交付信息
     * @param $info
     * @return bool
     * @throws \Exception
     */
    public function updateOrganizationDeliverInfo($info)
    {
        if (!array_key_exists('orgname', $info)) {
            throw new \Exception("租户企业号不能为空");
        }

        //根据GUID取相应的人员信息
        $deliverInfo = ErpUserRepository::getUserById(ArrayHelper::getValue($info, "deliver_guid"));
        $info["deliver"] = $deliverInfo["UserName"];
        $info["deliver_phone"] = StringHelper::getLastMobilePhone($deliverInfo["MobilePhone"]);
        $info["deliver_email"] = $deliverInfo["Email"];

        $customManagerInfo = ErpUserRepository::getUserById(ArrayHelper::getValue($info, "custom_manager_guid"));
        $info["custom_manager"] = $customManagerInfo["UserName"];
        $info["custom_manager_phone"] = StringHelper::getLastMobilePhone($customManagerInfo["MobilePhone"]);
        $info["custom_manager_email"] = $customManagerInfo["Email"];


        return $this->_organizationRepository->updateOrganizationDeliverInfo($info);
    }

    /**
     * 获取租户列表（select2控件）
     * @param string $keyword 搜索关键字
     * @param string $limit 只显示几条记录
     * @return array
     */
    public function getOrganizationListForSelect2($keyword, $limit)
    {
        return $this->_organizationRepository->getOrganizationListForSelect2($keyword, $limit);
    }

    /**
     * 获取租户实例
     */
    public function getRDSDatabaseInstanceList()
    {
        //获取配置库中默认的创建租户实例ID
        /** @var ConfigSettingService $configSettingService */
        $configSettingService = \Yii::createObject(ConfigSettingService::className());
        $instanceIds = $configSettingService->getValue(self::DEFAULT_CREATE_ORGANIZATION_DATABASE_INSTANCE_ID);

        $instanceIds = explode(';', $instanceIds);

        $rdsInfo = \Yii::$app->databaseInstance->getInstanceListByInstanceIds($instanceIds);

        $rdsList = [];
        foreach ($rdsInfo as $rds) {
            $rdsListInfo = [
                'InstanceId' => $rds['instance_id'],
                'InstanceName' => $rds['instance_id'],
            ];
            $rdsList [] = $rdsListInfo;
        }
        return $rdsList;
    }

    /**
     * 字节转GB
     * @param $size
     * @param int $digits
     * @return float
     */
    private function _size2gb($size, $digits = 0)
    { //digits，要保留几位小数
        $base = 1024;
        $i = floor(log($size, $base));//字节数对1024取对数，值向下取整。
        return round($size / pow($base, $i), $digits);
    }

    /**
     * 创建租户
     * @param $uniqueName
     * @param $friendlyName
     * @param $instanceId
     * @param $email
     * @param $orderCode
     * @param $isTest
     * @return bool
     * @throws \yii\base\InvalidConfigException
     */
    public function createOrganization($uniqueName, $friendlyName, $instanceId, $email, $orderCode = "", $isTest = 0)
    {
        set_time_limit(120);
        // 参数校验
        $verifyArgumentResult = $this->_verifyArgument($uniqueName, $friendlyName, $instanceId, $email);
        if ($verifyArgumentResult === false) {
            return $this->setAndReturn(__LINE__, $this->getErrMsg());
        }

        // 生成DBName，获取实例中是否包含当前数据库，如果有重名，则加随机数重命名
        $databaseName = $this->makeDataBaseName($instanceId, $uniqueName);
        $password = substr($uniqueName, 0, 2) . rand(100000, 999999);
        $passwordMD5 = md5($password);

        // 创建租户数据库
        $createDatabaseResult = $this->_createOrganizationDataBase($instanceId, $databaseName);
        if ($createDatabaseResult === false) {
            return $this->setAndReturn(__LINE__, '创建数据库失败');
        }

        //获取实例详情
        $rdsConnection = \Yii::$app->databaseInstance->getInstanceByInstanceId($instanceId);

        //获取空库
        $emptyDatabase = $this->_organizationRepository->getOrgByUniqueName(self::EMPTY_DATABASE_NAME);
        $emptyConnection = $this->_splitOrgConnectionString($emptyDatabase->ConnectionString);//拆分租户的数据库连接字符串

        //根据默认空库还原新建的租户库
        if (!$this->_importEmptyDbTemplate($emptyConnection, $rdsConnection, $databaseName, $instanceId)) {
            Yii::error('导入默认数据库失败');
            return $this->setAndReturn(__LINE__, '导入默认数据库失败');
        }

        $newConnection = [
            'server' => $rdsConnection['host'],
            'port' => $rdsConnection['port'],
            'database' => $databaseName,
            'uid' => $emptyConnection['uid'],
            'pwd' => $emptyConnection['pwd'],
            'AllowUserVariables' => True,
        ];

        $trans = ConfigEntity::getDb()->beginTransaction();
        try {

            // 新增租户（配置数据库中相关表的操作）
            /** @var ConfigSettingService $configSettingService */
            $configSettingService = \Yii::createObject(ConfigSettingService::className());
            $asyncServiceScheduleKey = $configSettingService->getValue(self::ASYNC_SERVICE_SCHEDULE_KEY);

            $orgId = StringHelper::uuid();
            $organizationArr = [
                'ConnectionString' => 'server=' . $rdsConnection['host'] . ';port=' . $rdsConnection['port'] . ';database=' . $databaseName . ';uid=' . $emptyConnection['uid'] . ';pwd=' . $emptyConnection['pwd'] . ';AllowUserVariables=True',
                'DatabaseName' => $databaseName,
                'InstanceName' => $instanceId,
                'InstanceId' => $instanceId,
                'FriendlyName' => $friendlyName,
                'UniqueName' => $uniqueName,
                'Id' => $orgId,
                'State' => 1,
                'Email' => $email,
                'UpgradeSerialNumber' => $emptyDatabase->UpgradeSerialNumber,
                'VersionNumber' => $emptyDatabase->VersionNumber,
                'ScheduleKey' => $asyncServiceScheduleKey,
            ];
            $createResult = $this->_organizationRepository->createOrganization($organizationArr, $newConnection, $passwordMD5);
            if ($createResult === false) {
                $trans->rollBack();
                return $this->setAndReturn(__LINE__, '新增租户失败');
            }

            //如果是测试租户，往测试租户表插入记录
            if ($isTest == 1){
                $addTestResult = $this->_organizationRepository->addTestOrganization($uniqueName);
                if ($addTestResult === false) {
                    $trans->rollBack();
                    return $this->setAndReturn(__LINE__, self::getErrMsg());
                }
            }

            // 默认启用拓客中心新模式、项目级客户确认模式
            $insertFunctionUpgradeResult = $this->_organizationRepository->insertFunctionUpgrade($orgId);
            if ($insertFunctionUpgradeResult === false) {
                $trans->rollBack();
                return $this->setAndReturn(__LINE__, '默认启用拓客中心新模式、项目级客户确认模式失败');
            }

            // 初始化ERP接口
            $initERPInterfaceResult = $this->initERPInterface($orgId);
            if ($initERPInterfaceResult === false) {
                $trans->rollBack();
                return $this->setAndReturn(__LINE__, '初始化ERP接口失败');
            }

            //关联申请单号
            if (!empty($orderCode)) {
                /* @var $flowCenterService FlowCenterService */
                $flowCenterService = \Yii::createObject(FlowCenterService::className());
                $ret = $flowCenterService->addOrg($orgId, $orderCode, $friendlyName, $uniqueName, $password);
                if (false === $ret) {
                    $trans->rollBack();
                    return $this->setAndReturn(-1, $flowCenterService->getErrMsg());
                }
            }

            $trans->commit();

        } catch (Exception $e) {
            $trans->rollBack();
            Yii::error((string)$e);
            //删除创建的RDS数据库
            $deleteResult = \Yii::$app->databaseInstance->deleteDatabase($instanceId, $databaseName);
            if ($deleteResult === false) {
                Yii::error('创建完数据库，执行其他操作出现异常回滚时删除数据库失败');
            }
            return $this->setAndReturn(__LINE__, (string)$e);
        }

        // 初始化数据（插入默认用户）
        $initDefaultDataBaseDataResult = $this->_initDefaultDataBaseData($friendlyName, $uniqueName, $passwordMD5);
        if ($initDefaultDataBaseDataResult === false) {
            return $this->setAndReturn(__LINE__, '初始化数据（插入默认用户）失败');
        }

        //自动灰度巡检
        try {
            $initProcessResult = $this->initProcess($uniqueName);
            if ($initProcessResult === false) {
                return $this->setAndReturn(__LINE__, '自动灰度巡检失败');
            }
        } catch (\Exception $e) {
            //删除租户
            $newOrganization = $this->_organizationRepository->getOrgByUniqueName($uniqueName);
            $this->deleteOrganization($newOrganization->Id, '创建租户时，自动灰度巡检失败');
            return $this->setAndReturn(__LINE__, '自动灰度巡检失败' . (string)$e);
        }

        // 发送通知邮件
        $content = $this->_genEMailContent($uniqueName, $friendlyName, $password);
        $sendEmailResult = $this->sendEmail($email, "明源地产云客账户信息通知", $content);
        if ($sendEmailResult === false) {
            return $this->setAndReturn(__LINE__, '发送邮件失败');
        }

        // 云商城添加客户，添加失败时，邮件通知指定人
        $marcketAddCustomerResult = $this->marketAddCustomer($uniqueName, $friendlyName);
        if ($marcketAddCustomerResult === false) {
            //邮件通知云商城客户添加失败
            $this->sendEmailWhenAddYunMarketDefeated($uniqueName, $friendlyName);
        }

        return true;
    }

    /**
     * 添加云商城开发商
     * 先按名称，存在则加uniqueName，再存在则加时间戳
     * @param $uniqueName
     * @param $friendlyName
     * @return bool
     */
    private function marketAddCustomer($uniqueName, $friendlyName)
    {
        $hasSame = $this->isMarketHasSameName($friendlyName);
        if($hasSame === true){
            $friendlyName = $friendlyName . '_' . $uniqueName;
            $hasSame2 = $this->isMarketHasSameName($friendlyName);
            if($hasSame2 === true){
                $friendlyName = $friendlyName . '_' . time();
            }
        }

        return $this->marketAdd($uniqueName, $friendlyName);
    }

    /**
     * 添加云商城客户失败时，发邮件通知
     * @param $uniqueName
     * @param $friendlyName
     */
    public function sendEmailWhenAddYunMarketDefeated($uniqueName, $friendlyName)
    {
        $to = \Yii::$app->params['sentEmailWhenAddYunMarketDefeated'];
        $subject = '请处理：云商城添加客户失败';
        $body = "租户Code：{$uniqueName}，租户Name：{$friendlyName}。云商城接口返回信息：{$this->getErrMsg()}";

        $this->sendEmail($to, $subject, $body);
    }

    /**
     * 检查云商城中是否存在同名的开发商
     * @param $friendlyName
     * @return bool
     */
    public function isMarketHasSameName($friendlyName)
    {
        /** @var ConfigSettingService $configSettingService */
        $configSettingService = \Yii::createObject(ConfigSettingService::className());
        $baseUrl = $configSettingService->getValue(self::MARKET_BASE_URL);

        $getOrderDetailUrl = $baseUrl . '/market/customer/get';
        $param = [
            'name' => $friendlyName
        ];

        $curl = new Curl();
        //请求https需要用ipv4
        $curl->setOption(CURLOPT_IPRESOLVE, CURL_IPRESOLVE_V4);
        $jwtToken = 'Bearer ' . $this->_makeMarketToken();
        $headers['Authorization'] = $jwtToken;
        $curl->setHeaders($headers);
        $response = $curl->post($getOrderDetailUrl, $param);
        $ret = json_decode($response, true);

        if (empty($ret) || $ret['errcode'] != 0 || empty($ret['data'])) {
            return false;
        }

        return true;
    }

    public function marketAdd($uniqueName, $friendlyName)
    {
        /** @var ConfigSettingService $configSettingService */
        $configSettingService = \Yii::createObject(ConfigSettingService::className());
        $baseUrl = $configSettingService->getValue(self::MARKET_BASE_URL);

        $getOrderDetailUrl = $baseUrl . '/market/customer/add-developer';
        $param = [
            'cust_code' => $uniqueName,
            'seller_code' => 20,
            'name' => $friendlyName,
            'shortname' => $friendlyName,
            'address' => '暂无',
            'contact' => '暂无',
            'mobile' => '18888888888'
        ];

        $curl = new Curl();
        //请求https需要用ipv4
        $curl->setOption(CURLOPT_IPRESOLVE, CURL_IPRESOLVE_V4);
        $jwtToken = 'Bearer ' . $this->_makeMarketToken();
        $headers['Authorization'] = $jwtToken;
        $curl->setHeaders($headers);
        $response = $curl->post($getOrderDetailUrl, $param);
        $ret = json_decode($response, true);

        if (empty($ret) || $ret['errcode'] != 0) {
            Yii::error($response);
            return $this->setAndReturn(__LINE__, $response);
        }

        return true;
    }

    private function _makeMarketToken()
    {
        /** @var ConfigSettingService $configSettingService */
        $configSettingService = \Yii::createObject(ConfigSettingService::className());
        $secret = $configSettingService->getValue(self::MARKET_JWT_SECRET);
        $key = $configSettingService->getValue(self::MARKET_JWT_KEY);

        $payload = [
            "iss" => $key,
            "exp" => time() + 3600 // time in the future
        ];

        $encoded = JWTHelper::encode($payload, $secret);

        return $encoded;

    }

    /**
     * 老租户灰度巡检
     * @param $orgCode
     * @param bool $forceInit
     * @return bool
     */
    public function oldOrganizationInitProcess($orgCode, $forceInit = false)
    {
        $organization = $this->_organizationRepository->getOrgByUniqueName($orgCode);
        if (!$organization) {
            echo sprintf('找不到当前租户[%s]信息', $orgCode), PHP_EOL;

            return false;
        }

        if ($this->checkXunjianDbExist($organization) && $forceInit === false) {
            echo sprintf('当前租户[%s]已执行过初始化，强制执行，请设置第二个参数为true，强制初始化将清空数据库。', $orgCode), PHP_EOL;

            return false;
        }

        $setGreyLevelResult = $this->ensureHasSetGreyLevel($organization);
        if ($setGreyLevelResult === false) {
            echo sprintf('ensureHasSetGreyLevel失败'), PHP_EOL;
            return false;
        }

        $hasCreateMenuResult = $this->ensureHasCreateMenu($organization);
        if ($hasCreateMenuResult === false) {
            echo sprintf('ensureHasCreateMenu失败'), PHP_EOL;
            return false;
        }

        $hasCreatedXunjianDbResult = $this->ensureHasCreatedXunjianDb($organization);
        if ($hasCreatedXunjianDbResult === false) {
            echo sprintf('创建巡检数据库失败'), PHP_EOL;
            return false;
        }

        return true;

    }

    /**
     * 创建租户时，灰度巡检
     * @param $orgCode
     * @param bool $forceInit
     * @return bool
     */
    private function initProcess($orgCode, $forceInit = false)
    {
        $organization = $this->_organizationRepository->getOrgByUniqueName($orgCode);
        if (!$organization) {
            \Yii::error('租户不存在');
            return $this->setAndReturn(__LINE__, '租户不存在');
        }

        if ($this->checkXunjianDbExist($organization) && $forceInit !== 'true') {
            \Yii::error('巡检库已存在');
            return $this->setAndReturn(__LINE__, '巡检库已存在');
        }

        if ($this->checkXunjianDbExist($organization) && $forceInit) {
            \Yii::error('巡检库已存在');
            return $this->setAndReturn(__LINE__, '巡检库已存在');
        }

        $setGreyLevelResult = $this->ensureHasSetGreyLevel($organization);
        if ($setGreyLevelResult === false) {
            \Yii::error('ensureHasSetGreyLevel失败');
            return $this->setAndReturn(__LINE__, 'ensureHasSetGreyLevel失败');
        }

        $hasCreatedXunjianDbResult = $this->ensureHasCreatedXunjianDb($organization);
        if ($hasCreatedXunjianDbResult === false) {
            \Yii::error('ensureHasCreatedXunjianDb失败');
            return $this->setAndReturn(__LINE__, 'ensureHasCreatedXunjianDb失败');
        }

        return true;
    }

    private function ensureHasCreatedXunjianDb(MyscrmOrganizationEntity $org)
    {
        $databaseName = $this->buildXunjianDbName($org->DatabaseName);
        try {
            $data = $this->assignDatabaseInstance($org->UniqueName, $databaseName);
            if (empty($data)) {
                \Yii::error(sprintf('分配巡检数据库%s创建失败', $org->UniqueName));
                return false;
            }

            /* @var $xunjianDatabaseService MyscrmXunjianDatabaseService */
            $xunjianDatabaseService = Yii::createObject(MyscrmXunjianDatabaseService::className());
            $connection = $xunjianDatabaseService->getDatabaseConnectionByOrgCode($org->UniqueName);

            $createdXunjianDbResult = $this->createRdsXunjianDb($org, $connection->instanceId);
            if ($createdXunjianDbResult === false) {
                \Yii::error(sprintf('数据库%s创建失败', $connection->dbname));
                return false;
            }

            $importXunjianDbTemplateResult = $this->importXunjianDbTemplate($connection, $databaseName);
            if ($importXunjianDbTemplateResult === false) {
                \Yii::error(sprintf('导入默认数据库%s失败', $connection->dbname));
                return false;
            }

            return true;

        } catch (Exception $e) {
            $this->deleteDatabaseInstance($org->UniqueName);
            \Yii::error((string)$e);
            return false;
        }
    }

    private function deleteDatabaseInstance($orgCode)
    {
        /* @var $xunjianDatabaseService MyscrmXunjianDatabaseService */
        $xunjianDatabaseService = Yii::createObject(MyscrmXunjianDatabaseService::className());

        return $xunjianDatabaseService->deleteDatabaseInstance($orgCode);
    }

    /**
     * 导入巡检数据库结构
     * @param ConnectionModel $connection
     * @param                 $databaseName
     * @return bool
     */
    private function importXunjianDbTemplate(ConnectionModel $connection, $databaseName)
    {
        $sqlFile = Yii::getAlias(self::EMPTY_XUNJIAN_TEMPLATE_SQL);
        $this->ensureExistEmptyXunjianTemplate($sqlFile);

        sleep(30);

        $importCommand = sprintf(
            'mysql -h%s -u%s -p%s -P%s %s < %s',
            $connection->host,
            $connection->username,
            $connection->password,
            $connection->port,
            $connection->dbname,
            $sqlFile
        );

        $result = system($importCommand);
        sleep(5);

        return $result !== false;
    }

    /**
     * 确认存在最新的空巡检数据库模板
     * @param $sqlFile
     */
    private function ensureExistEmptyXunjianTemplate($sqlFile)
    {
        if (file_exists(($sqlFile))) {
            unlink($sqlFile);
        }

        //取空租户的数据库配置
        /* @var $xunjianDatabaseService MyscrmXunjianDatabaseService */
        $xunjianDatabaseService = Yii::createObject(MyscrmXunjianDatabaseService::className());
        /** @var ConnectionModel $emptyConnection */
        $emptyConnection = $xunjianDatabaseService->getDatabaseConnectionByOrgCode(self::EMPTY_ORG_CODE);

        if (YII_ENV == 'prod') {
            $command = 'mysqldump -h%s -u%s -p%s -P%s --default-character-set=utf8 --set-gtid-purged=OFF --routines %s > %s';
        } else {
            $command = 'mysqldump -h%s -u%s -p%s -P%s --default-character-set=utf8 --routines %s > %s';
        }

        $exportCommand = sprintf(
            $command,
            $emptyConnection->host,
            $emptyConnection->username,
            $emptyConnection->password,
            $emptyConnection->port,
            $emptyConnection->dbname,
            $sqlFile
        );

        system($exportCommand);

        sleep(3);

        if (!file_exists($sqlFile) || filesize($sqlFile) <= 0) {
            Yii::error('生成巡检默认模板失败');
        }
    }

    private function createRdsXunjianDb(MyscrmOrganizationEntity $org, $dBInstanceId)
    {
        $databaseName = $this->buildXunjianDbName($org->DatabaseName);

        //创建数据库
        $response = Yii::$app->databaseInstance->createDatabase($dBInstanceId, $databaseName);

        if ($response) {
            //创建成功后，赋权
            $grantResult = $this->grantAccountPrivilege($dBInstanceId, $databaseName);
            if ($grantResult === false) {
                \Yii::error('创建巡检数据库成功，但赋权失败');
                //赋权失败时，删除创建的数据库
                Yii::$app->databaseInstance->deleteDatabase($dBInstanceId, $databaseName);
                return $this->setAndReturn(__LINE__, '创建巡检数据库成功，但赋权失败');
            }

            return true;
        } else {
            \Yii::error('创建巡检数据库失败');
            return $this->setAndReturn(__LINE__, '创建巡检数据库失败');
        }
    }

    private function checkRdsXunjianDbExist($dBInstanceId, $databaseName)
    {
        return Yii::$app->databaseInstance->checkDatabaseExist($dBInstanceId, $databaseName);
    }

    private function grantAccountPrivilege($DBInstanceId, $DBName)
    {
        return Yii::$app->databaseInstance->grantAccountPrivilege($DBInstanceId, $DBName);

    }

    public function ensureHasCreateMenu(MyscrmOrganizationEntity $org)
    {
        $sql = "
        INSERT INTO `kfs_myfunction` (`FunctionGUID`, `FunctionName`, `HierarchyCode`, `ParentHierarchyCode`, `Level`, `IsLastStage`, `FunctionIcon`, `FunctionUrl`, `MainTable`, `Comments`, `IsDisabled`, `FunctionCode`, `Application`, `ParentFunctionGUID`, `Assembly`, `FunctionBigIcon`, `IsDummy`, `IsIncrement`, `FunctionType`, `FunctionHelpUrl`, `UseScope`, `PrimaryObjectTypeCode`, `IconClassUrl`, `IconClass`, `CreatedOn`, `CreatedBy`, `ModifiedOn`, `ModifiedBy`, `VersionNumber`, `Status`, `Scope`, `ApplyMode`)
SELECT 'bc632bdc-af6a-11e5-b308-00155d02c832','统计分析','0045','','0','1',NULL,NULL,NULL,NULL,'0','000029','0000','',NULL,NULL,'0','0','功能',NULL,'0',NULL,NULL,NULL,'2016-06-13 23:14:36',NULL,NULL,NULL,NULL,'0','18','0' FROM DUAL WHERE NOT EXISTS (SELECT 1 from `kfs_myfunction` WHERE `FunctionGUID`= 'bc632bdc-af6a-11e5-b308-00155d02c832');

INSERT INTO `kfs_myfunction` (`FunctionGUID`, `FunctionName`, `HierarchyCode`, `ParentHierarchyCode`, `Level`, `IsLastStage`, `FunctionIcon`, `FunctionUrl`, `MainTable`, `Comments`, `IsDisabled`, `FunctionCode`, `Application`, `ParentFunctionGUID`, `Assembly`, `FunctionBigIcon`, `IsDummy`, `IsIncrement`, `FunctionType`, `FunctionHelpUrl`, `UseScope`, `PrimaryObjectTypeCode`, `IconClassUrl`, `IconClass`, `CreatedOn`, `CreatedBy`, `ModifiedOn`, `ModifiedBy`, `VersionNumber`, `Status`, `Scope`, `ApplyMode`) 
SELECT 
'3d18c1d1-af6d-11e5-b308-00155d02c832','统计报表','0045.0001','0045','1','1',NULL,NULL,NULL,NULL,'0','00002901','0000','bc632bdc-af6a-11e5-b308-00155d02c832',NULL,NULL,'0','0','功能',NULL,'0',NULL,NULL,NULL,'2016-06-13 23:14:36',NULL,NULL,NULL,NULL,'0','18','0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 from `kfs_myfunction` WHERE `FunctionGUID`= '3d18c1d1-af6d-11e5-b308-00155d02c832');

INSERT INTO `kfs_myfunction` (`FunctionGUID`, `FunctionName`, `HierarchyCode`, `ParentHierarchyCode`, `Level`, `IsLastStage`, `FunctionIcon`, `FunctionUrl`, `MainTable`, `Comments`, `IsDisabled`, `FunctionCode`, `Application`, `ParentFunctionGUID`, `Assembly`, `FunctionBigIcon`, `IsDummy`, `IsIncrement`, `FunctionType`, `FunctionHelpUrl`, `UseScope`, `PrimaryObjectTypeCode`, `IconClassUrl`, `IconClass`, `CreatedOn`, `CreatedBy`, `ModifiedOn`, `ModifiedBy`, `VersionNumber`, `Status`, `Scope`, `ApplyMode`) 
SELECT 
'ed53debd-af6d-11e5-b308-00155d02c832','自助查询','0045.0002','0045','1','1',NULL,NULL,NULL,NULL,'0','00002902','0000','bc632bdc-af6a-11e5-b308-00155d02c832',NULL,NULL,'0','0','功能',NULL,'0',NULL,NULL,NULL,'2015-12-31 11:19:28',NULL,NULL,NULL,NULL,'0','18','0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 from `kfs_myfunction` WHERE `FunctionGUID`= 'ed53debd-af6d-11e5-b308-00155d02c832');

INSERT INTO `kfs_myaction` (`ObjectType`, `ActionGUID`, `ActionCode`, `ActionName`, `Comments`, `InvokeClass`, `InvokeFunction`, `Assembly`, `CreatedOn`, `CreatedBy`, `ModifiedOn`, `ModifiedBy`, `versionnumber`, `ApplyMode`)
SELECT 
'00002901','876ab611-af6e-11e5-b308-00155d02c832','00','查看我的报表','查看',NULL,NULL,NULL,'2016-06-13 23:14:37',NULL,NULL,NULL,'0','0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 from `kfs_myaction` WHERE `ActionGUID`= '876ab611-af6e-11e5-b308-00155d02c832');

INSERT INTO `kfs_myaction` (`ObjectType`, `ActionGUID`, `ActionCode`, `ActionName`, `Comments`, `InvokeClass`, `InvokeFunction`, `Assembly`, `CreatedOn`, `CreatedBy`, `ModifiedOn`, `ModifiedBy`, `versionnumber`, `ApplyMode`)
SELECT 
'00002901','8e7ab912-af6e-11e5-b308-00155d02c832','01','修改我的报表','修改',NULL,NULL,NULL,'2016-06-13 23:14:37',NULL,NULL,NULL,'0','0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 from `kfs_myaction` WHERE `ActionGUID`= '8e7ab912-af6e-11e5-b308-00155d02c832');

INSERT INTO `kfs_myaction` (`ObjectType`, `ActionGUID`, `ActionCode`, `ActionName`, `Comments`, `InvokeClass`, `InvokeFunction`, `Assembly`, `CreatedOn`, `CreatedBy`, `ModifiedOn`, `ModifiedBy`, `versionnumber`, `ApplyMode`)
SELECT 
'00002901','bfca4b71-af6e-11e5-b308-00155d02c832','03','添加到我的报表','添加到我的报表',NULL,NULL,NULL,'2016-06-13 23:14:37',NULL,NULL,NULL,'0','0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 from `kfs_myaction` WHERE `ActionGUID`= 'bfca4b71-af6e-11e5-b308-00155d02c832');

INSERT INTO `kfs_myaction` (`ObjectType`, `ActionGUID`, `ActionCode`, `ActionName`, `Comments`, `InvokeClass`, `InvokeFunction`, `Assembly`, `CreatedOn`, `CreatedBy`, `ModifiedOn`, `ModifiedBy`, `versionnumber`, `ApplyMode`) 
SELECT 
'00002901','c60d6d78-af6e-11e5-b308-00155d02c832','02','查看标准报表库','查看',NULL,NULL,NULL,'2016-06-13 23:14:37',NULL,NULL,NULL,'0','0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 from `kfs_myaction` WHERE `ActionGUID`= 'c60d6d78-af6e-11e5-b308-00155d02c832');

INSERT INTO `kfs_myaction` (`ObjectType`, `ActionGUID`, `ActionCode`, `ActionName`, `Comments`, `InvokeClass`, `InvokeFunction`, `Assembly`, `CreatedOn`, `CreatedBy`, `ModifiedOn`, `ModifiedBy`, `versionnumber`, `ApplyMode`)
SELECT 
'00002902','8975529c-4f20-11e6-bd92-8038bc0bb04f','01','创建报表','创建报表',NULL,NULL,NULL,'2016-07-27 19:19:10',NULL,NULL,NULL,'0','0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 from `kfs_myaction` WHERE `ActionGUID`= '8975529c-4f20-11e6-bd92-8038bc0bb04f');

INSERT INTO `kfs_myaction` (`ObjectType`, `ActionGUID`, `ActionCode`, `ActionName`, `Comments`, `InvokeClass`, `InvokeFunction`, `Assembly`, `CreatedOn`, `CreatedBy`, `ModifiedOn`, `ModifiedBy`, `versionnumber`, `ApplyMode`)
SELECT 
'00002902','9278df2a-4f20-11e6-bd92-8038bc0bb04f','00','查看','查看',NULL,NULL,NULL,'2016-07-27 19:19:10',NULL,NULL,NULL,'0','0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 from `kfs_myaction` WHERE `ActionGUID`= '9278df2a-4f20-11e6-bd92-8038bc0bb04f');

INSERT INTO `kfs_myaction` (`ObjectType`, `ActionGUID`, `ActionCode`, `ActionName`, `Comments`, `InvokeClass`, `InvokeFunction`, `Assembly`, `CreatedOn`, `CreatedBy`, `ModifiedOn`, `ModifiedBy`, `versionnumber`, `ApplyMode`)
SELECT 
'00002902','af320d73-4eea-11e6-be74-00155d02c832','02','编辑','编辑',NULL,NULL,NULL,'2016-07-27 19:19:10',NULL,NULL,NULL,'0','0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 from `kfs_myaction` WHERE `ActionGUID`= 'af320d73-4eea-11e6-be74-00155d02c832');

INSERT INTO `kfs_myaction` (`ObjectType`, `ActionGUID`, `ActionCode`, `ActionName`, `Comments`, `InvokeClass`, `InvokeFunction`, `Assembly`, `CreatedOn`, `CreatedBy`, `ModifiedOn`, `ModifiedBy`, `versionnumber`, `ApplyMode`) 
SELECT 
'00002902','b8c1d61b-4eea-11e6-be74-00155d02c832','03','删除','删除',NULL,NULL,NULL,'2016-07-27 19:19:10',NULL,NULL,NULL,'0','0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 from `kfs_myaction` WHERE `ActionGUID`= 'b8c1d61b-4eea-11e6-be74-00155d02c832');

INSERT INTO `kfs_myaction` (`ObjectType`, `ActionGUID`, `ActionCode`, `ActionName`, `Comments`, `InvokeClass`, `InvokeFunction`, `Assembly`, `CreatedOn`, `CreatedBy`, `ModifiedOn`, `ModifiedBy`, `versionnumber`, `ApplyMode`)
SELECT 
'00002902','bfdd8683-4eea-11e6-be74-00155d02c832','04','导出','导出',NULL,NULL,NULL,'2016-07-27 19:19:10',NULL,NULL,NULL,'0','0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 from `kfs_myaction` WHERE `ActionGUID`= 'bfdd8683-4eea-11e6-be74-00155d02c832');
        ";

        $connection = $this->getConnection($org->UniqueName);

        $db = new Connection(
            [
                'dsn' => 'mysql:host=' . $connection['server'] . ';dbname=' . $connection['database'] . ';port=' . (isset($connection["port"]) ? $connection["port"] : "3306"),
                'username' => $connection['uid'],
                'password' => $connection['pwd'],
                'charset' => 'utf8',
            ]
        );

        $query = $db->createCommand($sql);

        if ($query->execute() === false) {
            echo "插入菜单授权出错", PHP_EOL;
            echo $query->getRawSql(), PHP_EOL;
            exit;
        }
        return true;
    }

    /**
     * 分配数据库实例
     * @param $orgCode
     * @param $databaseName
     * @return bool
     * @internal param MyscrmOrganizationEntity $org
     */
    private function assignDatabaseInstance($orgCode, $databaseName)
    {
        /* @var $xunjianDatabaseService MyscrmXunjianDatabaseService */
        $xunjianDatabaseService = Yii::createObject(MyscrmXunjianDatabaseService::className());

        return $xunjianDatabaseService->assignDatabaseInstance($orgCode, $this->defaultXunjianDb->id, $databaseName);
    }

    private function ensureHasSetGreyLevel(MyscrmOrganizationEntity $org)
    {
        /** @var MyscrmFunctionupgradeService $functionUpgradeService */
        $functionUpgradeService = Yii::createObject(MyscrmFunctionupgradeService::className());

        return $functionUpgradeService->enableFunction($org->Id, self::FUNCTION_NAME);
    }

    private function buildXunjianDbName($databaseName)
    {
        return sprintf('%s_xunjian', $databaseName);
    }

    /**
     * @param MyscrmOrganizationEntity $org
     * @return bool
     */
    private function checkXunjianDbExist(MyscrmOrganizationEntity $org)
    {
        /* @var $xunjianDatabaseService MyscrmXunjianDatabaseService */
        $xunjianDatabaseService = Yii::createObject(MyscrmXunjianDatabaseService::className());
        $database = $xunjianDatabaseService->getDatabaseInstance($org->UniqueName);

        return $database ? true : false;
    }

    /**
     * 检查当前环境是否可执行
     * @return bool
     */
    private function checkExecutableEnv()
    {
        return YII_ENV != 'dev';
    }

    public function getConnection($orgName)
    {
        return $this->_organizationRepository->getConnection($orgName);
    }

    /**
     * 发送邮件通知
     * @param $uniqueName
     * @param $friendlyName
     * @param $password
     * @return string
     */
    private function _genEMailContent($uniqueName, $friendlyName, $password)
    {
        $loginUrl = 'http://qmyxcg.myscrm.com.cn/login.shtml';

        $content = $friendlyName . '，您好！<br>
<br />
欢迎您注册明源地产云客<br>
<br />
您的企业账户和密码如下：<br>
<br />
用户名：' . $uniqueName . '<br>
密码：' . $password . '<br>
<br />
要立即登录，单击  <A href=\"' . $loginUrl . '\" target=_blank>' . $loginUrl . '</A>
<br />
<br />

<br />
<br />
地址：深圳市南山区高新科技园南区高新南一道中科大厦9层 邮编：518057
<br />
电话：0755-86309791-8082   总机：0755-86309788
<br />
传真：0755-86309797';

        return $content;
    }

    /**
     * 发送邮件
     * @param $email
     * @param $subject
     * @param $body
     * @return bool
     */
    public function sendEmail($email, $subject, $body)
    {
        return EmailHelper::send($email, $subject, $body);
    }

    /**
     * 初始化ERP接口
     * @param $orgId
     * @return bool
     */
    public function initERPInterface($orgId)
    {
        $authenticationinfoMapping = [];
        $addressMapping = [];
        $erpServiceMapping = [];

        $initERPAuthenticationinfoResult = $this->_initERPAuthenticationinfo($authenticationinfoMapping, $orgId);
        if ($initERPAuthenticationinfoResult === false) {
            return false;
        }

        $initERPAddressResult = $this->_initERPAddress($authenticationinfoMapping, $addressMapping, $orgId);
        if ($initERPAddressResult === false) {
            return false;
        }

        $initERPServiceResult = $this->_initERPService($addressMapping, $erpServiceMapping, $orgId);
        if ($initERPServiceResult === false) {
            return false;
        }

        return true;
    }


    private function _initERPAuthenticationinfo(&$authenticationinfoMapping, $orgId)
    {
        /** @var MyscrmErpInterfaceService $erpInterfaceService */
        $erpInterfaceService = Yii::createObject(MyscrmErpInterfaceService::className());

        $initOnOrgCreated = true;
        $templateArr = $erpInterfaceService->getErpauthenticationinfotemplateList($initOnOrgCreated);

        $sqlAuth = '';
        foreach ($templateArr as $template) {
            $newAuthenticationInfoTemplateId = StringHelper::uuid();
            $authenticationinfoMapping[$template['AuthenticationInfoTemplateId']] = $newAuthenticationInfoTemplateId;

            //组织SQL批量插入数据库
            $dateNow = date("Y-m-d H:m:s");
            $exitsServiceName = $erpInterfaceService->isExistsErpauthenticationinfo($template['AuthName'], $orgId);
            if (!$exitsServiceName) {
                $sqlAuth .= sprintf("insert into `myscrm_erpauthenticationinfo` (AuthenticationInfoId,AuthName,encrypt_servurl,encrypt_username,encrypt_pwd,encrypt_ishttps,CreatedOn,organizationId) values ('%s','%s','%s','%s','%s','%s','%s','%s');", $newAuthenticationInfoTemplateId, $template['AuthName'], $template['Encrypt_Servurl'], $template['Encrypt_Username'], $template['Encrypt_Pwd'], $template['Encrypt_IsHttps'], $dateNow, $orgId);
            }
        }

        if ($sqlAuth != '') {
            $dataToAsyncTaskResult = $this->_organizationRepository->executeSqlConfig($sqlAuth);
            if ($dataToAsyncTaskResult === false) {
                Yii::error('初始化ERP接口失败：插入myscrm_erpauthenticationinfo表；报错SQL：' . $sqlAuth);
                return $this->setAndReturn(__LINE__, '初始化ERP接口失败：插入myscrm_erpauthenticationinfo表');
            }
        }

        return true;
    }

    private function _initERPAddress($authenticationinfoMapping, &$addressMapping, $orgId)
    {
        /** @var MyscrmErpInterfaceService $erpInterfaceService */
        $erpInterfaceService = Yii::createObject(MyscrmErpInterfaceService::className());

        $initOnOrgCreated = true;
        $templateArr = $erpInterfaceService->getErpserviceaddresstemplateList($initOnOrgCreated);

        $sqlAddress = '';
        foreach ($templateArr as $template) {
            $newAddressTemplateId = StringHelper::uuid();
            $addressMapping[$template['AddressTemplateId']] = $newAddressTemplateId;

            //组织SQL批量插入数据库
            $dateNow = date("Y-m-d H:m:s");
            $exitsServiceName = $erpInterfaceService->isExistsErpserviceaddress($template['AddressName'], $orgId);
            if (!$exitsServiceName) {
                $sqlAddress .= sprintf("insert into `myscrm_erpserviceaddress` (AddressId,AddressName,AddressFrienldyName,Address,AuthenticationInfoId,CreatedOn,organizationId) values ('%s','%s','%s','%s','%s','%s','%s');", $newAddressTemplateId, $template['AddressName'], $template['AddressFrienldyName'], $template['Address'], $this->_changeGuidToString($template['AuthenticationInfoId'], $authenticationinfoMapping), $dateNow, $orgId);
            }
        }

        if ($sqlAddress != '') {
            $dataToAsyncTaskResult = $this->_organizationRepository->executeSqlConfig($sqlAddress);
            if ($dataToAsyncTaskResult === false) {
                Yii::error('初始化ERP接口失败：插入myscrm_erpserviceaddress表；报错SQL：' . $sqlAddress);
                return $this->setAndReturn(__LINE__, '初始化ERP接口失败：插入myscrm_erpserviceaddress表');
            }
        }

        return true;
    }

    private function _initERPService($addressMapping, $erpServiceMapping, $orgId)
    {
        /** @var MyscrmErpInterfaceService $erpInterfaceService */
        $erpInterfaceService = Yii::createObject(MyscrmErpInterfaceService::className());

        $initOnOrgCreated = true;
        $templateArr = $erpInterfaceService->getErpservicetemplateList($initOnOrgCreated);

        $sqlData = '';
        foreach ($templateArr as $template) {
            $newERPServiceTemplateId = StringHelper::uuid();
            $erpServiceMapping[$template['ERPServiceTemplateId']] = $newERPServiceTemplateId;

            //组织SQL批量插入数据库
            $dateNow = date("Y-m-d H:m:s");
            $beginDate = $template['BeginDateTime'] == null ? $dateNow : $template['BeginDateTime'];
            $OperationTypeId = $template['OperationTypeId'] == null ? self::EMPTYGUID : $template['OperationTypeId'];

            if ($template['ServiceCategory'] == self::IMMEDIATELY) {
                $exitsServiceName = $erpInterfaceService->isExistsErpservice($template['ServiceName'], $orgId);
                if (!$exitsServiceName) {
                    $sqlData .= sprintf("insert into `myscrm_erpservice` (ERPServiceId,OrganizationId,ServiceName,ServiceFriendlyName,AddressId,ServiceCategory,Enabled,Apiaddress,Applications,CreatedOn)
                values('%s','%s','%s','%s','%s',%s,'%s','%s','%s','%s');", $newERPServiceTemplateId, $orgId, $template['ServiceName'], $template['ServiceFriendlyName'], $this->_getGuidMapping($template['AddressId'], $addressMapping), 1, 1, $template['Apiaddress'], $template['Applications'], $dateNow);
                }
            } else {
                $recurrenceRule = 'FREQ=' . $this->changeTimeSpanType($template['TimeSpanType']) . ';INTERVAL=' . $template['TimeSpan'];
                $myAsyncOperationId = StringHelper::uuid();
                //插入myscrm_AsyncOperation表
                $sqlData .= sprintf("insert into `myscrm_AsyncOperation` (myAsyncOperationId,Name,OperationType,RecurrencePattern,ScheduleTime,OrganizationId,CreatedOn,CorrelationId,CorrelationUpdatedTime,Depth,Data,State,Status) values ('%s','%s','%s','%s','%s','%s','%s','%s','%s',%s,'%s',%s,%s);", $myAsyncOperationId, $template['ServiceFriendlyName'], $OperationTypeId, $recurrenceRule, $beginDate, $orgId, $dateNow, $myAsyncOperationId, $dateNow, 0, '', 1, 10);
                //插入myscrm_erpservice表
                $sqlData .= sprintf("insert into `myscrm_erpservice` (ERPServiceId,OrganizationId,ServiceName,ServiceFriendlyName,AddressId,ServiceCategory,myAsyncOperationId,enabled,Apiaddress,Applications,CreatedOn) values ('%s','%s','%s','%s','%s',%s,'%s',%s,'%s','%s','%s');", $newERPServiceTemplateId, $orgId, $template['ServiceName'], $template['ServiceFriendlyName'], $this->_getGuidMapping($template['AddressId'], $addressMapping), 2, $myAsyncOperationId, 1, $template['Apiaddress'], $template['Applications'], $dateNow);
            }
        }

        //配置：公共客户回收接口
        $sqlData .= sprintf("insert into `myscrm_erpservice` (`ERPServiceId`,`OrganizationId`,`ServiceName`,`ServiceFriendlyName`,`AddressId`,`ServiceCategory`,`myAsyncOperationId`,`Enabled`,`Applications`,`CreatedOn`) values ('%s','%s','%s','%s','%s',%s,'%s',%s,'%s','%s');", StringHelper::uuid(), $orgId, 'ScrmApi.YdxsAsyncService.RecoveryCstAsyncService', 'V3移动销售-自动回收客户', StringHelper::uuid(), 2, StringHelper::uuid(), 1, 5002, date("Y-m-d H:m:s"));

        if ($sqlData != '') {
            $dataToImmediatelyResult = $this->_organizationRepository->executeSqlConfig($sqlData);
            if ($dataToImmediatelyResult === false) {
                Yii::error('初始化ERP接口失败，报错SQL：' . $sqlData);
                return $this->setAndReturn(__LINE__, '初始化ERP接口失败');
            }
        }

        return true;
    }

    private function changeTimeSpanType($timeSpanType)
    {
        switch (intval($timeSpanType)) {
            case 0:
                return 'YEARLY';
                break;
            case 1:
                return 'MONTHLY';
                break;
            case 2:
                return 'WEEKLY';
                break;
            case 3:
                return 'DAILY';
                break;
            case 4:
                return 'HOURLY';
                break;
            case 5:
                return 'MINUTELY';
                break;
            default:
                return 'HOURLY';
        }
    }

    private function _getGuidMapping($changeValue, $guidMapping)
    {
        if ($changeValue == null || $changeValue == self::EMPTYGUID) {
            return self::EMPTYGUID;
        }

        if ($guidMapping != null && isset($guidMapping[$changeValue])) {
            return $guidMapping[$changeValue];
        }

        return $changeValue;
    }

    private function _changeGuidToString($changeValue, $guidMapping)
    {
        $changeResult = '';
        if ($changeValue != null && $changeValue != self::EMPTYGUID) {
            if (is_array($guidMapping) && !empty($guidMapping) && isset($guidMapping[$changeValue])) {
                $changeResult = $guidMapping[$changeValue];
            } else {
                $changeResult = $changeValue;
            }
        }

        return $changeResult;
    }

    /**
     * 连接新建的数据库，创建管理员账号
     * @param array $connection
     * @param $friendlyName
     * @param $uniqueName
     * @param $password
     * @return bool
     */
    private function _initDefaultDataBaseData($friendlyName, $uniqueName, $password)
    {
        //连接新建的数据库
        if ($this->orgInit($uniqueName)) {
            //删除从空库中带过来的数据
            $sqlDeleteEmptyUser = "delete from myuser where UserCode='" . self::EMPTY_DATABASE_NAME . "'";
            $sqlDeleteEmptyUserResult = $this->_organizationRepository->executeSql($sqlDeleteEmptyUser);
            if ($sqlDeleteEmptyUserResult === false) {
                Yii::error('RDS数据库创建后无法初始化用户及集团公司信息，报错SQL：' . $sqlDeleteEmptyUser);
                return false;
            }

            //插入一条数据
            $sqlInsertUser = "insert into myuser (UserGuid,UserCode,UserName,BUGUID,Password,IsDisabeld,IsAdmin,DepartmentGUID) values ('" . StringHelper::uuid() . "','" . $uniqueName . "','系统管理员','11B11DB4-E907-4F1F-8835-B9DAAB6E1F23','" . $password . "',0,1,'11B11DB4-E907-4F1F-8835-B9DAAB6E1F23')";
            $sqlInsertUserResult = $this->_organizationRepository->executeSql($sqlInsertUser);
            if ($sqlInsertUserResult === false) {
                Yii::error('RDS数据库创建后无法初始化用户及集团公司信息，报错SQL：' . $sqlInsertUser);
                return false;
            }

            //更新企业名称
            $sqlUpdateUserUnit = "update myBusinessUnit set BUName = '" . $friendlyName . "', BUFullName = '" . $friendlyName . "' where BUGUID = '11B11DB4-E907-4F1F-8835-B9DAAB6E1F23'";
            $sqlUpdateUserUnitResult = $this->_organizationRepository->executeSql($sqlUpdateUserUnit);
            if ($sqlUpdateUserUnitResult === false) {
                Yii::error('RDS数据库创建后无法初始化用户及集团公司信息，报错SQL：' . $sqlUpdateUserUnit);
                return false;
            }
        }

        return true;
    }


    /**
     * 导入默认数据库
     * @param array $emptyConnection 空库的连接信息
     * @param array $rdsConnection 新库的连接信息
     * @param $databaseName
     * @param $instanceId
     * @return bool
     */
    private function _importEmptyDbTemplate($emptyConnection, $rdsConnection, $databaseName, $instanceId)
    {
        $sqlFile = Yii::getAlias(self::EMPTY_CREATEORGANIZATION_TEMPLATE_SQL);
        $makeEmptyDatabaseTemplateResult = $this->_makeEmptyDatabaseTemplate($emptyConnection, $sqlFile);//生成空库SQL模板
        if ($makeEmptyDatabaseTemplateResult === false) {
            Yii::error('生成空库SQL模板失败');
            return $this->setAndReturn(__LINE__, '生成空库SQL模板失败');
        }

        // 检测新库是否创建就绪
        $grantedResult = $this->_makeSureDbHasGranted($instanceId, $databaseName);
        if ($grantedResult === false) {
            Yii::error('给创建的数据库授权失败');
            return $this->setAndReturn(__LINE__, '给创建的数据库授权失败');
        }

        $importCommand = sprintf(
            'mysql -h%s -u%s -p%s -P%s %s < %s',
            $rdsConnection['host'],//指定实例的连接地址
            $emptyConnection['uid'],
            $emptyConnection['pwd'],
            $rdsConnection['port'],//指定实例的端口
            $databaseName,
            $sqlFile
        );

        $result = system($importCommand);
        sleep(5);

        if ($result === false) {
            return false;
        }
        return true;
    }

    /**
     * 检查数据库的权限
     * @param $instanceId
     * @param $databaseName
     * @return bool
     */
    private function _makeSureDbHasGranted($instanceId, $databaseName)
    {
        return $this->_grantAccountPrivilege($instanceId, $databaseName);
    }

    /**
     * 创建空库的sql模板
     * @param array $emptyConnection
     * @param string $sqlFile 模板地址
     * @return bool
     */
    private function _makeEmptyDatabaseTemplate($emptyConnection, $sqlFile)
    {
        if (file_exists(($sqlFile))) {
            unlink($sqlFile);
        }

        //取空数据库配置
        if (YII_ENV == 'prod') {
            $command = 'mysqldump -h%s -u%s -p%s -P%s --default-character-set=utf8 --set-gtid-purged=OFF --routines %s > %s';
        } else {
            $command = 'mysqldump -h%s -u%s -p%s -P%s --default-character-set=utf8 --routines %s > %s';
        }

        $exportCommand = sprintf(
            $command,
            $emptyConnection['server'],
            $emptyConnection['uid'],
            $emptyConnection['pwd'],
            3306,
            $emptyConnection['database'],
            $sqlFile
        );

        system($exportCommand);

        sleep(5);

        if (!file_exists($sqlFile) || filesize($sqlFile) <= 0) {
            return false;
        }

        return true;
    }

    /**
     * 拆分租户的数据库连接字符串
     * @param string $connectionString server=ykqadatabasein.mysql.rds.aliyuncs.com;database=myscrm_empty_file;uid=myscrm_cn;pwd=myscrm_cn;AllowUserVariables=True;
     * @return array|null
     */
    private function _splitOrgConnectionString($connectionString)
    {
        $arr = explode(';', $connectionString);
        $result = [];
        foreach ($arr as $key => $item) {
            $parts = explode("=", $item);
            if (count($parts) > 1) {
                $result[$parts[0]] = $parts[1];
            }
        }
        return $result;
    }

    /**
     * 创建租户数据库
     * @param $instanceId
     * @param $databaseName
     * @return bool
     */
    private function _createOrganizationDataBase($instanceId, $databaseName)
    {
        // 在指定RDS实例上新增指定名称的租户库
        $createDatabaseResult = \Yii::$app->databaseInstance->createDatabase($instanceId, $databaseName);
        if ($createDatabaseResult) {//创建数据库成功
            //给数据库授读写权限
            if ($this->_grantAccountPrivilege($instanceId, $databaseName) === false) {
                \Yii::error('在实例（' . $instanceId . '）上创建数据库（' . $databaseName . '）成功，但给数据库授读写权限失败');

                \Yii::$app->databaseInstance->deleteDatabase($instanceId, $databaseName);

                return false;
            }
            return true;
        } else {
            \Yii::error('在实例（' . $instanceId . '）上创建数据库（' . $databaseName . '）失败');
        }

        return false;
    }

    private function _grantAccountPrivilege($instanceId, $databaseName)
    {
        return Yii::$app->databaseInstance->grantAccountPrivilege($instanceId, $databaseName);
    }

    /**
     * 生成数据名称
     * @param $instanceId
     * @param $uniqueName
     * @return string
     */
    public function makeDataBaseName($instanceId, $uniqueName)
    {
        $databaseName = 'myscrm_' . $uniqueName;
        $nameExist = \Yii::$app->databaseInstance->checkDatabaseExist($instanceId, $uniqueName);

        if ($nameExist) {//实例上存在同名数据库，则加随机数生成新的名称
            $databaseName = $databaseName . rand(100000, 999999);
        }

        return $databaseName;
    }

    /**
     * 参数校验
     * @param string $uniqueName 企业号
     * @param string $friendlyName 企业名称
     * @param string $instanceId
     * @param string $email
     * @return void|bool
     */
    private function _verifyArgument($uniqueName, $friendlyName, $instanceId, $email)
    {
        //是否为空
        if ($uniqueName == '' || $friendlyName == '' || $instanceId == '' || $email == '') {
            return $this->setAndReturn(__LINE__, '参数缺失');
        }
        //企业号或者企业名称是否重名
        $isExistByUniqueName = $this->_organizationRepository->getOrgByUniqueName($uniqueName);
        if ($isExistByUniqueName) {
            return $this->setAndReturn(__LINE__, '该企业号已经存在');
        }
        $isExistByFriendlyName = $this->_organizationRepository->getOrgByFriendlyName($uniqueName);
        if ($isExistByFriendlyName) {
            return $this->setAndReturn(__LINE__, '该企业名称已经存在');
        }
        //企业号格式：英文或下划线开头，由英文字符、下划线或数字组成的长度为2到30个字符串。
        if (preg_match('/^[a-zA-Z_][a-zA-Z_0-9]{2, 30}/', $uniqueName)) {
            return $this->setAndReturn(__LINE__, '企业号格式错误，请控制为：英文或下划线开头，由英文字符、下划线或数字组成的长度为2到30个字符串');
        }

    }

    /**
     * 删除租户
     * @param $orgId
     * @param $reason
     * @return bool
     */
    public function deleteOrganization($orgId, $reason)
    {
        $organizationEntity = $this->_organizationRepository->getById($orgId);

        if (!$organizationEntity) {
            Yii::error('删除失败：找不到此租户');
            return $this->setAndReturn(__LINE__, '删除失败：找不到此租户');
        }

        //判断实例上是否有该数据库
        $nameExist = \Yii::$app->databaseInstance->checkDatabaseExist($organizationEntity->InstanceId, $organizationEntity->DatabaseName);

        if ($nameExist) {
            // 在指定RDS实例上删除RDS数据库
            $deleteDatabaseResult = \Yii::$app->databaseInstance->deleteDatabase($organizationEntity->InstanceId, $organizationEntity->DatabaseName);
            if ($deleteDatabaseResult === false) {
                Yii::error('删除Rds数据库失败');
                return $this->setAndReturn(__LINE__, '删除Rds数据库失败');
            }
        }

        //判断是否有巡检库，有则删除
        $deleteXunjianDatabaseResult = $this->deleteXunjianDbIfExist($organizationEntity);
        if ($deleteXunjianDatabaseResult === false) {
            Yii::error('删除Rds巡检数据库失败');
            return $this->setAndReturn(__LINE__, '删除Rds巡检数据库失败');
        }

        /** @var MyscrmOperlogRepository $myscrmOperlogRepository */
        $myscrmOperlogRepository = Yii::createObject(MyscrmOperlogRepository::className());

        /** @var PigcmsUsersRepository $pigcmsUsersRepository */
        $pigcmsUsersRepository = Yii::createObject(PigcmsUsersRepository::className());

        $transaction = ConfigEntity::getDb()->beginTransaction();
        try {
            // 删除组织
            $deleteOrganizationResult = $this->_organizationRepository->deleteById($orgId);
            if ($deleteOrganizationResult === false) {
                $transaction->rollBack();
                Yii::error('删除租户表记录失败');
                return $this->setAndReturn(__LINE__, '删除租户表记录失败');
            }
            // 添加租户的操作日志
            $addOperlogResult = $myscrmOperlogRepository->addOperLog($orgId, $reason, 4);
            if ($addOperlogResult === false) {
                $transaction->rollBack();
                Yii::error('添加租户的操作日志记录失败');
                return $this->setAndReturn(__LINE__, '添加租户的操作日志记录失败');
            }
            // 删除组织授权
            $deleteOrganizationToAuthorizeResult = $this->_organizationRepository->deleteOrganizationToAuthorizeByOrganizationId($orgId);
            if ($deleteOrganizationToAuthorizeResult === false) {
                $transaction->rollBack();
                Yii::error('删除租户组织授权记录失败');
                return $this->setAndReturn(__LINE__, '删除租户组织授权记录失败');
            }

            // 删除users
            $deletePigCmsUserResult = $pigcmsUsersRepository->deleteByOrganizationId($orgId);
            if ($deletePigCmsUserResult === false) {
                $transaction->rollBack();
                Yii::error('删除pigcms_user记录失败');
                return $this->setAndReturn(__LINE__, '删除pigcms_user记录失败');
            }

            //删除租户，记录到数据删除池
            $this->saveDeleteData($orgId);

            $deleteTestOrgResult = $this->_organizationRepository->deleteTestOrganization($organizationEntity->UniqueName);
            if($deleteTestOrgResult === false){
                $transaction->rollBack();
                return $this->setAndReturn(__LINE__, '删除测试租户失败');
            }

            $transaction->commit();
            return true;

        } catch (Exception $e) {
            $transaction->rollBack();
            Yii::error((string)$e);
            return $this->setAndReturn(__LINE__, '删除配置库中该租户相关数据失败');
        }
    }

    /**
     * 如果存在巡检数据库则删除
     * @param MyscrmOrganizationEntity $organizationEntity
     * @return bool
     * @throws \yii\base\InvalidConfigException
     */
    public function deleteXunjianDbIfExist(MyscrmOrganizationEntity $organizationEntity)
    {
        //判断是否有巡检库，有则删除
        if ($this->checkXunjianDbExist($organizationEntity)) {
            /* @var $xunjianDatabaseService MyscrmXunjianDatabaseService */
            $xunjianDatabaseService = Yii::createObject(MyscrmXunjianDatabaseService::className());
            $xunjianDatabase = $xunjianDatabaseService->getDatabaseInstance($organizationEntity->UniqueName);

            /** @var MyscrmDatabaseInstanceConfigService $myscrmDatabaseInstanceConfigService */
            $myscrmDatabaseInstanceConfigService = Yii::createObject(MyscrmDatabaseInstanceConfigService::className());
            $instanceId = $myscrmDatabaseInstanceConfigService->getConfigById($xunjianDatabase->database_instance_id);
            // 在指定RDS实例上删除RDS巡检数据库
            $deleteXunjianDatabaseResult = \Yii::$app->databaseInstance->deleteDatabase($instanceId->instance_id, $organizationEntity->DatabaseName . '_xunjian');
            if ($deleteXunjianDatabaseResult === false) {
                Yii::error('删除巡检数据库失败');
                return $this->setAndReturn(__LINE__, '删除巡检数据库失败');
            }
            $this->deleteDatabaseInstance($organizationEntity->UniqueName);
        }
        return true;
    }

    /**
     * 开启/禁用
     * @param $orgId
     * @param $state
     * @param $reason
     * @return bool
     * @throws \yii\base\InvalidConfigException
     * @throws \yii\db\Exception
     */
    public function updateOrganizationState($orgId, $state, $reason)
    {
        /** @var MyscrmOperlogRepository $myscrmOperlogRepository */
        $myscrmOperlogRepository = Yii::createObject(MyscrmOperlogRepository::className());

        $transaction = ConfigEntity::getDb()->beginTransaction();
        try {
            $addOperlogResult = $myscrmOperlogRepository->addOperLog($orgId, $reason, 4);
            if ($addOperlogResult === false) {
                $transaction->rollBack();
                Yii::error('添加租户的操作日志记录失败');
                return $this->setAndReturn(__LINE__, '添加租户的操作日志记录失败');
            }

            $updateResult = $this->_organizationRepository->updateStatesByOrganizationId($orgId, $state);
            if ($updateResult === false) {
                $transaction->rollBack();
                Yii::error('更改租户状态失败');
                return $this->setAndReturn(__LINE__, '更改租户状态失败');
            }

            $transaction->commit();
            return true;
        } catch (Exception $e) {
            $transaction->rollBack();
            Yii::error((string)$e);
            return $this->setAndReturn(__LINE__, '更新状态失败');
        }
    }

    public function resetPwd($orgId, $pwd, $email)
    {
        $passwordMd5 = md5($pwd);
        $organizationEntity = $this->_organizationRepository->getById($orgId);

        if (!$organizationEntity) {
            Yii::error('重置密码：找不到此租户');
            return $this->setAndReturn(__LINE__, '重置密码：找不到此租户');
        }

        /** @var PigcmsUsersRepository $pigcmsUsersRepository */
        $pigcmsUsersRepository = Yii::createObject(PigcmsUsersRepository::className());

        $transaction = ConfigEntity::getDb()->beginTransaction();
        try {
            $updateOrganizationResult = $this->_organizationRepository->updateEmailOrganizationId($email, $organizationEntity);
            if ($updateOrganizationResult === false) {
                $transaction->rollBack();
                Yii::error('更改租户Email失败');
                return $this->setAndReturn(__LINE__, '更改租户Email失败');
            }

            $updatePigCmsUsersResult = $pigcmsUsersRepository->updateByUserName($passwordMd5, $organizationEntity->UniqueName);
            if ($updatePigCmsUsersResult === false) {
                $transaction->rollBack();
                Yii::error('更改租户PigcmsUsers失败');
                return $this->setAndReturn(__LINE__, '更改租户PigcmsUsers失败');
            }
            $transaction->commit();
        } catch (Exception $e) {
            $transaction->rollBack();
            Yii::error((string)$e);
            return $this->setAndReturn(__LINE__, '更新状态失败');
        }

        $this->orgInit($organizationEntity->UniqueName);

        $sqlUpdateUser = "update myUser set password = '" . $passwordMd5 . "' where usercode = '" . $organizationEntity->UniqueName . "'";
        $sqlUpdateUserResult = $this->_organizationRepository->executeSql($sqlUpdateUser);
        if ($sqlUpdateUserResult === false) {
            Yii::error('更新myuser失败，报错SQL：' . $sqlUpdateUser);
            return false;
        }

        // 发送通知邮件
        $content = $this->_genEMailContent($organizationEntity->UniqueName, $organizationEntity->FriendlyName, $pwd);
        $sendEmailResult = $this->sendEmail($email, "明源地产云客账户信息通知", $content);
        if ($sendEmailResult === false) {
            return $this->setAndReturn(__LINE__, '发送邮件失败');
        }

        return true;
    }

    /**
     * 赠送短信
     * @param $organizationId
     * @param $count
     * @param $amount
     * @return bool
     */
    public function addSmsQuota($organizationId, $count, $amount)
    {
        /** @var MyscrmProductquotaRepository $myscrmProductquotaRepository */
        $myscrmProductquotaRepository = Yii::createObject(MyscrmProductquotaRepository::className());

        $trans = ConfigEntity::getDb()->beginTransaction();
        try {

            $quotaInfo = $myscrmProductquotaRepository->getOrgQuoInfo($organizationId, self::BASE_PRODUCT_CODE);
            if ($quotaInfo) {//更新
                $updateResult = $myscrmProductquotaRepository->updateInfo($quotaInfo['PoductQuotaId'], $count);
                if ($updateResult === false) {
                    $trans->rollBack();
                    return $this->setAndReturn(__LINE__, '赠送短信失败');
                }
            } else {//新增
                $addResult = $myscrmProductquotaRepository->addInfo($organizationId, $count);
                if ($addResult === false) {
                    $trans->rollBack();
                    return $this->setAndReturn(__LINE__, '赠送短信失败');
                }
            }

            //赠送短信需要往充值记录里添加记录
            if ($count > 0) {
                $addLogResult = $this->addRechargeLog($organizationId, $count, $amount);
                if($addLogResult === false){
                    $trans->rollBack();
                    return $this->setAndReturn(__LINE__, '添加充值记录失败');
                }
            }

            $trans->commit();

        }catch (\Exception $e){
            $trans->rollBack();
            return $this->setAndReturn(__LINE__, '赠送短信失败');
        }

        //更新租户短信不足是否发送通知的状态
        $this->updateNoticeStatus($organizationId);

        return true;
    }

    /**
     * 为租户添加充值短信记录
     * @param $organizationId
     * @param $count
     * @param $amount
     * @return bool
     * @throws \Exception
     */
    public function addRechargeLog($organizationId, $count, $amount)
    {
        $orgEntity = $this->_organizationRepository->getOrgById($organizationId);
        if(empty($orgEntity)){
            throw new \Exception("商户不存在");
        }
        return $this->_organizationRepository->addRechargeLog($orgEntity->UniqueName, $count, $amount);
    }

    /**
     * 更新租户的已发送通知状态
     * @param $orgId
     * @param int $isNotice 0未通知，1已通知
     * @return bool
     */
    public function updateNoticeStatus($orgId, $isNotice = 0)
    {
        try {
            $orgInfo = $this->_organizationRepository->getOrgById($orgId);
            $orgInfo->IsNotice = $isNotice;
            $orgInfo->save();
        } catch (\Exception $e) {
            \Yii::error((string)$e);
        }
    }

    public function getSmsQuota($organizationId)
    {
        /** @var MyscrmProductquotaRepository $myscrmProductquotaRepository */
        $myscrmProductquotaRepository = Yii::createObject(MyscrmProductquotaRepository::className());

        $quotaInfo = $myscrmProductquotaRepository->getOrgQuoInfo($organizationId, self::BASE_PRODUCT_CODE);

        $ret = [];
        if ($quotaInfo) {
            $ret = $quotaInfo;
        }
        return $ret;
    }

    /**
     * 租户db连接
     */
    private function orgInit($orgcode)
    {
        //租户库连接初始化
        $org = new MyscrmOrganizationRepository();
        $connection = $org->getConnection($orgcode);

        if ($connection === null) {
            return false;
        }
        KfsDbEntity::$db = null;
        KfsDbEntity::$dbConnection = $connection;
        KfsDbEntity::$orgcode = $orgcode;
        return true;
    }

    private function orgDbClose()
    {
        if (KfsDbEntity::getDb()) {
            KfsDbEntity::getDb()->close();
        }
    }

    /**
     * 修改所有400账号密码
     * @param $pwd
     * @return bool
     */
    public function updateAll400Pwd($pwd)
    {
        try {
            $pwdMd5 = md5($pwd);
            $allOrganization = $this->_organizationRepository->getAllOrganizationCodes();

            foreach ($allOrganization as $orgCode) {
                try {
                    if ($this->orgInit($orgCode)) {//切换租户连接
                        $sql = "update myuser set `Password`='" . $pwdMd5 . "' where `UserCode`='mysoftadmin' ";

                        $this->_organizationRepository->executeSql($sql);
                        $this->orgDbClose();
                    }
                } catch (\Exception $e) {
                    echo sprintf('%s 执行失败', $orgCode), PHP_EOL;
                    continue;
                }
            }
        } catch (\Exception $e) {
            Yii::error((string)$e);
            return $this->setAndReturn(__LINE__, '修改失败' . (string)$e);
        }
        return true;
    }


    /**
     * 项目授权概况列表
     * @param $areaId
     * @param $disable
     * @param $orgState
     * @param $keyword
     * @param bool $isPage
     * @param int $pageIndex
     * @param int $pageSize
     * @return array
     * @throws \yii\base\InvalidConfigException
     */
    public function getOrgProjectAuthList($areaId, $disable, $orgState, $keyword, $isPage = true, $pageIndex = 1, $pageSize = 10)
    {
        $data = $this->getAllAuthProject($areaId, $disable, $orgState, $keyword);

        //装拼返回数据
        $organizationIds = ArrayHelper::getColumn($data, 'OrganizationId');
        $organizationIds = array_unique($organizationIds);

        $contractProjectIds = ArrayHelper::getColumn($data, 'id');

        $projectIds = ArrayHelper::getColumn($data, 'ProjId');
        $projectIds = array_unique($projectIds);

        $contractIds = ArrayHelper::getColumn($data, 'contractId');
        $contractIds = array_unique($contractIds);
        $orgCount = count($organizationIds);

        $orgList = $this->_organizationRepository->getListByIds($organizationIds, $isPage, $pageIndex, $pageSize);
        /** @var MyscrmAuthorizeService $myscrmAuthorizeService */
        $myscrmAuthorizeService = Yii::createObject(MyscrmAuthorizeService::className());
        $orgCodes = ArrayHelper::getColumn($orgList, 'UniqueName');
        $contractApplicationProject = $myscrmAuthorizeService->getOrgContractApplicationProject($orgCodes);

        $data = $this->processProjectApplications($orgList, $contractIds, $projectIds, $contractApplicationProject, $contractProjectIds);
        return ['data' => array_values($data), 'total' => $orgCount];
    }

    /**
     * 处理应用项目关系 并返回
     * @param $orgList
     * @param $contractIds
     * @param $projectIds
     * @param $contractApplicationProject
     * @param $contractProjectIds
     * @return array
     * @throws \yii\base\InvalidConfigException
     */
    private function processProjectApplications($orgList, $contractIds, $projectIds, $contractApplicationProject, $contractProjectIds)
    {
        $result = [];
        //是否设置了不再邮件提醒
        /** @var MyscrmOrganizationRemindRepository $remindRepository */
        $remindRepository = Yii::createObject(MyscrmOrganizationRemindRepository::className());
        $remindArr = $remindRepository->getRemindDetails($contractIds, $projectIds);
        $remindList = [];
        foreach ($remindArr as $item) {
            $contractId = $item['contractId'];
            $projectId = $item['projectId'];
            $appCode = $item['appCode'];
            $isXq = $item['isXq'];

            $remindList[$contractId][$projectId][$appCode] = $isXq;
        }

        //获取所有合同授权信息
        /* @var $myscrmContractRepository MyscrmContractRepository */
        $myscrmContractRepository = Yii::createObject(MyscrmContractRepository::className());
        $allContracts = $myscrmContractRepository->getContractByIds($contractIds);

        foreach ($orgList as $orgKey => $org) {
            $resultItem = [];

            if (!isset($contractApplicationProject[$org['UniqueName']])) {
                unset($orgList[$orgKey]);
                continue;
            }
            $projectApplicationContractList = $contractApplicationProject[$org['UniqueName']];

            $projects = [];
            $contracts = [];
            foreach ($projectApplicationContractList as $projectApplication) {
                if (!in_array($projectApplication['id'], $contractProjectIds)) {
                    //过滤掉没权限查看的
                    continue;
                }

                $isSetRemind = '-1';
                $contractId = $projectApplication['contractId'];
                $projectId = $projectApplication['ProjId'];
                $appCode = $projectApplication['applicationCode'];
                if (isset($remindList[$contractId][$projectId][$appCode])) {
                    $isXq = $remindList[$contractId][$projectId][$appCode];
                    $isSetRemind = (string)$isXq;
                }

                $contractItem = [
                    'ContractNum' => $projectApplication['contractNum'],
                    'ContractId' => $projectApplication['contractId'],
                    'PartyA' => $projectApplication['contractName'],
                    'BeginOn' => $projectApplication['contractBeginOn'],
                    'EndOn' => $projectApplication['contractEndOn'],
                    'Status' => $projectApplication['status'],
                    'isSetRemind' => $isSetRemind,
                    "AreaId" => $allContracts[$projectApplication['contractId']]["AreaId"],
                    "Area" => $allContracts[$projectApplication['contractId']]["Area"],
                    "ContractManageId" => $allContracts[$projectApplication['contractId']]["ContractManageId"],
                    "appName" => $projectApplication["applicationName"],
                    "appCode" => $projectApplication["applicationCode"],
                    "authDate" => $projectApplication["CreatedOn"],
                    "authToken" => $projectApplication["wxName"],
                    "isOverdue" => $projectApplication["isOverdue"],
                    "overdueTime" => $projectApplication["overdueTime"],
                    "overdueReason" => $projectApplication["overdueReason"],
                ];

                $contractItem["hasReceivable"] = 0;
                //判断是否有回款信息
                if (!empty($contractItem["ContractManageId"])) {
                    if (isset($this->hasReceivable[$contractItem["ContractManageId"]])) {
                        $hasReceivable = $this->hasReceivable[$contractItem["ContractManageId"]];
                    } else {
                        $hasReceivable = ContractReceivableRepository::hasReceivable($contractItem["ContractManageId"]);
                        $this->hasReceivable[$contractItem["ContractManageId"]]=$hasReceivable;
                    }
                    if ($hasReceivable) {
                        $contractItem["hasReceivable"] = 1;
                    }
                }


                if (!isset($contracts[$projectApplication["ProjId"]])) {
                    $contracts[$projectApplication["ProjId"]] = [];
                }
                array_push($contracts[$projectApplication["ProjId"]], $contractItem);

                //设置项目信息
                if (!isset($projects[$projectApplication["ProjId"]])) {
                    $projects[$projectApplication["ProjId"]] = [
                        "ProjName" => $projectApplication["ProjName"],
                        "ProjId" => $projectApplication["ProjId"]
                    ];
                }
            }

            //拼接$contracts 和 $projects
            $applications = [];
            foreach ($contracts as $projectId => $contractItem) {
                //根据Status倒排
                ArrayHelper::multisort($contractItem, "Status", SORT_DESC);
                $applicationItem = $projects[$projectId];
                $applicationItem["contracts"] = $contractItem;
                array_push($applications, $applicationItem);
            }

            $resultItem["Id"] = $org["Id"];
            $resultItem["UniqueName"] = $org["UniqueName"];
            $resultItem["FriendlyName"] = $org["FriendlyName"];
            $resultItem["State"] = $org["State"];
            $resultItem["CreatedOn"] = $org["CreatedOn"];
            $resultItem["AliasName"] = $org["AliasName"];
            $resultItem["applications"] = $applications;
            array_push($result, $resultItem);
        }
        return $result;
    }

    /**
     * 取出所有项目
     * @param $areaId
     * @param $disable
     * @param $orgState
     * @param $keyword
     * @param string $orgId
     * @return array
     */
    public function getAllAuthProject($areaId, $disable, $orgState, $keyword, $orgId = "")
    {
        //筛选时选择了所有区域
        $chooseAllArea = true;
        if (!empty($areaId)) {
            $chooseAllArea = false;
        }
        $sql = "select 
                  distinct(t1.id),t1.ProjId,t1.ProjName,t2.Id as OrganizationId,t1.contractId
                  from myscrm_contract_project as t1 
                  left join `myscrm_organization` as t2 on t1.`orgcode`=t2.`UniqueName` 
                  left join `myscrm_project_contact` as t4 on t4.org_id=t2.Id and t4.project_id=t1.ProjId 
                  where t2.Id!='' and t2.IsCanUpgrade!=0 and t1.contractId!='' and t1.ProjId!=''";
        //并不需要连接所有表
        if (!empty($keyword)) {
            $sql = "select 
                  distinct(t1.id),t1.ProjId,t1.ProjName,t2.Id as OrganizationId,t1.contractId
                  from myscrm_contract_project as t1 
                  left join `myscrm_organization` as t2 on t1.`orgcode`=t2.`UniqueName` 
                  left join myscrm_contract as t3 on t3.`ContractId`=t1.`contractId` 
                  left join `myscrm_project_contact` as t4 on t4.org_id=t2.Id and t4.project_id=t1.ProjId 
                  where t2.Id!='' and t2.IsCanUpgrade!=0 and t1.contractId!='' and t1.ProjId!=''";
        }

        $params = [];
        //是否禁用租户
        if ($disable != '') {
            $sql .= " and t2.State=:disable ";
            $params[":disable"] = $disable;
        }
        //过期非过期筛选
        if (!empty($orgState)) {
            //禁用状态
            if ($orgState == "overdue") {
                $sql .= " and t1.isOverdue=1 ";
            } else {
                switch ($orgState) {
                    case 'normal':
                        //正常，3个月后才到期
                        $status = 1;
                        break;
                    case 'will_expire':
                        //即将到期
                        $status = 2;
                        break;
                    case 'expire':
                        //已到期
                        $status = 3;
                        break;
                }
                $sql .= " and t1.status={$status} and t1.isOverdue!=1 ";
            }
        }
        //关键字搜索
        if (!empty($keyword)) {
            $sql .= " and (t2.`UniqueName` LIKE :keyword or t2.`FriendlyName` like :keyword or t3.ContractNum like :keyword or t1.ProjName like :keyword)";
            $params[":keyword"] = "%" . $keyword . "%";
        }

        $userEntity = ErpInterfaceService::getUserByCode(\Yii::$app->user->identity->usercode);
        $isAreaOwner = $this->_checkIsAreaOwner($userEntity);
        $is400Role = $this->_checkIs400Role($userEntity);

        //不是区域负责人，不是400项目授权查看角色，也不是指定的领导，只能看交付顾问或者客户经理是自己的数据
        if (!$is400Role && !$isAreaOwner && !in_array($userEntity->UserGUID, \Yii::$app->params['allAuthUserId'])) {
            $sql .= " and (t4.`user_guid`=:user_guid or t2.CustomManagerGUID=:user_guid or t2.DeliverGUID=:user_guid) ";
            $params[":user_guid"] = $userEntity->UserGUID;
            if (!$chooseAllArea) {
                //获取指定权限内的区域
                $areaId = $this->getAuthAreaId($areaId, $is400Role);
                if (!empty($areaId)) {
                    if (is_array($areaId)) {
                        $areaId = implode("','", $areaId);
                    }
                    $areaId = "'" . $areaId . "'";
                    $sql .= " and t1.areaId in ($areaId)";
                }
            }
        } else {
            //获取指定权限内的区域
            $areaId = $this->getAuthAreaId($areaId, $is400Role);
            if (!empty($areaId)) {
                if (is_array($areaId)) {
                    $areaId = implode("','", $areaId);
                }
                $areaId = "'" . $areaId . "'";
                if ($chooseAllArea) {
                    //如果是选择全部区域 则还需要将交付信息匹配上的也加入
                    $sql .= " and (t1.areaId in ($areaId) OR (t4.`user_guid`=:user_guid or t2.CustomManagerGUID=:user_guid or t2.DeliverGUID=:user_guid) )";
                    $params[":user_guid"] = $userEntity->UserGUID;
                } else {
                    $sql .= " and t1.areaId in ($areaId)";
                }
            }

        }

        //过滤标记为测试的租户数据
        $allTestOrgList = $this->_organizationRepository->getAllTestOrg();
        if(!empty($allTestOrgList)) {
            $testOrgCodes = ArrayHelper::getColumn($allTestOrgList, 'orgcode', false);
            $testOrgIds = implode("','", $testOrgCodes);
            $sql .= " and t2.`UniqueName` not in ('{$testOrgIds}') ";
        }

        //只获取单个租户的信息
        if (!empty($orgId)) {
            $sql .= " and t2.Id = :orgId";
            $params[":orgId"] = $orgId;
        }
        return ConfigEntity::getDb()->createCommand($sql)->bindValues($params)->queryAll();
    }

    /**
     * 获取areaId
     * @param $areaId
     * @param $is400Role
     * @return mixed
     */
    private function getAuthAreaId($areaId, $is400Role)
    {
        #权限过滤
        //三类用户：
        //普通用户，只能看到和自己相关的数据，自己是交付顾问或客户经理；
        //区域负责人，可以看到本区域的所有数据，可以更新交付信息；
        //领导层（姚大、剑峰等）、400角色可以查看所有区域的数据
        $userEntity = ErpInterfaceService::getUserByCode(\Yii::$app->user->identity->usercode);
        #配置文件里的用户显示全部区域数据
        if (!in_array($userEntity->UserGUID, \Yii::$app->params['allAuthUserId'])
            && $userEntity->BUGUID != '11b11db4-e907-4f1f-8835-b9daab6e1f23' &&
            $userEntity->BUGUID != '6e2a7816-8e27-4b19-9428-628259bd8eda' && !$is400Role
        ) {
            $buGUIDList = [];
            $parttimeBUGUIDs = MyscrmParttimeBusinessEntity::find()->where(['user_guid' => $userEntity->UserGUID])->all();
            foreach ($parttimeBUGUIDs as $k => $v) {
                $buGUIDList = array_merge($buGUIDList, ErpInterfaceService::getSubBuGuidsByBuGuid($v->bu_guid));
            }
            $areaIds = array_merge($buGUIDList, ErpInterfaceService::getSubBuGuidsByBuGuid($userEntity->BUGUID));

            //非配置文件里的用户查看全部数据时，只能查看自己区域的数据
            if ($areaId == '') {
                $areaId = $areaIds;
            }
        }
        return $areaId;
    }

    /**
     * 将结果转换为可供展示的列表
     * @param $orgList
     * @return array
     */
    private function parseAuthProjResult($orgList)
    {
        $result = [];
        foreach ($orgList as $list) {
            $contracts = $list["contracts"];
            $projList = [];
            foreach ($contracts as $contract) {
                $applications = $contract["applications"];
                foreach ($applications as $application) {
                    $projects = $application["projects"];
                    foreach ($projects as $project) {
                        $val = $contract;
                        unset($val["applications"]);
                        $val["appName"] = $application["FriendlyName"];
                        $val["authDate"] = $project["CreatedOn"];
                        $val["authToken"] = $project["wxName"];
                        if (isset($projList[$project["ProjName"]])) {
                            array_push($projList[$project["ProjName"]], $val);
                        } else {
                            $projList[$project["ProjName"]] = [$val];
                        }
                    }
                }
            }
            $temp = $list;
            unset($temp["contracts"]);
            $apps = [];
            foreach ($projList as $key => $proj) {
                $arr["ProjName"] = $key;
                $arr["contracts"] = $proj;
                array_push($apps, $arr);
            }
            $temp["applications"] = $apps;
            array_push($result, $temp);
        }
        return $result;
    }

    /**
     * 把项目绑定的应用组合成字符串
     * 元素格式：appCode + projectId，例：500203bfe9b5-288f-3083-b42d-4ba494e782da
     * @param $arr
     * @param $applications
     */
    private function __makeProjectAndApplication(&$arr, $applications)
    {
        if ($applications) {
            foreach ($applications as $app) {
                $appProjects = $app['projects'];
                if ($appProjects) {
                    foreach ($appProjects as $appProject) {
                        $arr[] = $app['UniqueName'] . $appProject['p_projectId'];
                    }
                }
            }
        }

    }

    /**
     * 过期状态转换
     * @param $endOn
     * @return string
     */
    private function __transStatus($endOn)
    {
        $today = date("Y-m-d");
        $month3 = date('Y-m-d', strtotime('+3 month'));

        if ($endOn < $today) {
            return '已过期';
        } elseif ($today <= $endOn && $endOn <= $month3) {
            return '即将过期';
        } else {
            return '正常';
        }
    }


    public function __orgProjectAuthListFindData($areaId, $disable, $orgState, $keyword)
    {
        $chooseAll = false;
        if ($areaId == "") {
            $chooseAll = true;
        }

        #权限过滤
        //三类用户：
        //普通用户，只能看到和自己相关的数据，自己是交付顾问或客户经理；
        //区域负责人，可以看到本区域的所有数据，可以更新交付信息；
        //领导层（姚大、剑峰等）可以查看所有区域的数据。
        $userEntity = ErpInterfaceService::getUserByCode(\Yii::$app->user->identity->usercode);
        $isAreaOwner = $this->_checkIsAreaOwner($userEntity);

        #配置文件里的用户显示全部区域数据
        if (!in_array($userEntity->UserGUID, \Yii::$app->params['allAuthUserId'])
            && $userEntity->BUGUID != '11b11db4-e907-4f1f-8835-b9daab6e1f23' &&
            $userEntity->BUGUID != '6e2a7816-8e27-4b19-9428-628259bd8eda'
        ) {
            $buGUIDList = [];
            $parttimeBUGUIDs = MyscrmParttimeBusinessEntity::find()->where(['user_guid' => $userEntity->UserGUID])->all();
            foreach ($parttimeBUGUIDs as $k => $v) {
                $buGUIDList = array_merge($buGUIDList, ErpInterfaceService::getSubBuGuidsByBuGuid($v->bu_guid));
            }
            $areaIds = array_merge($buGUIDList, ErpInterfaceService::getSubBuGuidsByBuGuid($userEntity->BUGUID));

            //非配置文件里的用户查看全部数据时，只能查看自己区域的数据
            if ($areaId == '') {
                $areaId = $areaIds;
            }

        }

        //查找数据
        $data = $this->_organizationRepository->getOrgProjectAuthList($userEntity, $isAreaOwner, $areaId, $disable, $orgState, $keyword, $chooseAll);
        return $data;
    }

    /**
     * 检查是否区域负责人
     * @param $userEntity
     * @return bool
     * @throws \yii\base\InvalidConfigException
     */
    private function _checkIsAreaOwner($userEntity)
    {
        /** @var RoleUserRepository $roleUserRepository */
        $roleUserRepository = Yii::createObject(RoleUserRepository::className());
        $roleInfo = $roleUserRepository->getRoleIdsByUserId($userEntity->UserGUID);
        if (in_array(15, $roleInfo)) {
            return true;
        }

        return false;
    }

    private function _checkIs400Role($userEntity)
    {
        /** @var RoleUserRepository $roleUserRepository */
        $roleUserRepository = Yii::createObject(RoleUserRepository::className());
        $roleList = $roleUserRepository->getRoleNameList($userEntity->UserGUID);

        $roleNames = ArrayHelper::getColumn($roleList['roleList'], 'role_name');

        if (in_array(self::PROJECT_AUTH_ROLE_NAME_400, $roleNames)) {
            return true;
        }

        return false;
    }

    /**
     * 导出
     * @param $areaId
     * @param $disable
     * @param $orgState
     * @param $keyword
     * @return mixed
     */
    public function exportOrgProjectAuthList($areaId, $disable, $orgState, $keyword)
    {

        $data = $this->getAllAuthProject($areaId, $disable, $orgState, $keyword);

        //装拼返回数据
        $organizationIds = ArrayHelper::getColumn($data, 'OrganizationId');
        $organizationIds = array_unique($organizationIds);

        $projectIds = ArrayHelper::getColumn($data, 'ProjId');
        $projectIds = array_unique($projectIds);

        $contractIds = ArrayHelper::getColumn($data, 'contractId');
        $contractIds = array_unique($contractIds);

        $orgList = $this->_organizationRepository->getListByIds($organizationIds, false);

        $objPHPExcel = new \PHPExcel();
        $activeSheet = $this->__getActiveSheet($objPHPExcel);
        $activeSheet->setTitle('项目授权明细表');

        //设置列标题
        $columnTitles = ['租户名称', '企业号', '状态', '创建时间', '区域', '客户经理', '客户经理电话', '交付顾问', '交付顾问电话', '合同号',
            '合同甲方', '租期开始', '租期结束', '应用名称', '项目名称', '使用情况', '禁用原因'];
        $columnIndex = 'A';
        foreach ($columnTitles as $key => $value) {
            $activeSheet->setCellValue($columnIndex . '1', $value);
            $activeSheet->getColumnDimension($columnIndex)->setWidth(15);
            $activeSheet->getStyle($columnIndex . '1')->getFont()->setBold(true);
            $columnIndex++;
        }
        $j = 2;

        /** @var MyscrmAuthorizeService $myscrmAuthorizeService */
        $myscrmAuthorizeService = Yii::createObject(MyscrmAuthorizeService::className());

        $orgCodes = ArrayHelper::getColumn($orgList, 'UniqueName');
        $contractApplicationProject = $myscrmAuthorizeService->getOrgContractApplicationProject($orgCodes);


        foreach ($orgList as $orgKey => &$org) {
            if (!isset($contractApplicationProject[$org['UniqueName']])) {
                unset($orgList[$orgKey]);
                continue;
            }

            //租户库取数据
            $projectApplicationContractList = $contractApplicationProject[$org['UniqueName']];

            ArrayHelper::multisort($projectApplicationContractList, ['contractNum', 'applicationName', 'ProjName'], SORT_ASC, SORT_NATURAL);
            foreach ($projectApplicationContractList as $item) {
                //过滤掉没权限查看
                if (!in_array($item["ProjId"], $projectIds) || !in_array($item['contractId'], $contractIds)) {
                    continue;
                }
                //根据项目ID 和 orgcode 获取交付信息
                $orgcode = $item["orgcode"];
                $projectId = $item["ProjId"];

                $item['contractCustomManager'] = "";
                $item['contractCustomManagerPhone'] = "";
                $item['contractDeliver'] = "";
                $item['contractDeliverPhone'] = "";

                if (isset($this->exportContact[$orgcode . "_" . $projectId])) {
                    $contactInfo = $this->exportContact[$orgcode . "_" . $projectId];
                } else {
                    $contactInfo = $this->getExportContact($orgcode, $projectId);
                    $this->exportContact[$orgcode . "_" . $projectId] = $contactInfo;
                }

                if (!empty($contactInfo)) {
                    $item = array_merge($item, $contactInfo);
                }

                $i = 'A';
                $activeSheet->setCellValue($i . $j, $org['FriendlyName']); //租户名称
                $i++;
                $activeSheet->setCellValue($i . $j, $org['UniqueName']); //企业号
                $i++;
                $activeSheet->setCellValue($i . $j, $org['State'] == 1 ? '启用' : '禁用'); //状态
                $i++;
                $activeSheet->setCellValue($i . $j, $org['CreatedOn']); //创建时间
                $i++;
                $areaName = ErpBusinessUnitRepository::getBunameByBuguid($item['areaId']);
                $activeSheet->setCellValue($i . $j, $areaName === FALSE ? '' : $areaName); //合同所属区域
                $i++;
                $activeSheet->setCellValue($i . $j, $item['contractCustomManager']); //客户经理
                $i++;
                $activeSheet->setCellValue($i . $j, $item['contractCustomManagerPhone']); //客户经理电话
                $i++;
                $activeSheet->setCellValue($i . $j, $item['contractDeliver']); //交付顾问
                $i++;
                $activeSheet->setCellValue($i . $j, $item['contractDeliverPhone']); //交付顾问电话
                $i++;
                $activeSheet->setCellValue($i . $j, $item['contractNum']); //合同号
                $i++;
                $activeSheet->setCellValue($i . $j, $item['contractName']); //合同甲方
                $i++;
                if (!empty($item['contractBeginOn'])) {
                    $item['contractBeginOn'] = date("Y-m-d", strtotime($item['contractBeginOn']));
                }
                $activeSheet->setCellValue($i . $j, $item['contractBeginOn']); //租期开始
                $i++;
                if (!empty($item['contractBeginOn'])) {
                    $item['contractEndOn'] = date("Y-m-d", strtotime($item['contractEndOn']));
                }
                $activeSheet->setCellValue($i . $j, $item['contractEndOn']); //租期结束
                $i++;
                $activeSheet->setCellValue($i . $j, $item['applicationName']); //应用名称
                $i++;
                $activeSheet->setCellValue($i . $j, $item['ProjName']); //项目名称
                $i++;
                $activeSheet->setCellValue($i . $j, $item['IsDisplay'] == '1' ? '禁用' : '启用'); //使用情况
                $i++;
                //如果其中包含 "=" 则会当做计算符 ,防止报错替换为空
                $item['Reason'] = str_replace("=", "", $item['Reason']);
                $activeSheet->setCellValue($i . $j, $item['Reason']); //禁用原因

                $j++;

            }
        }


        $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);


        $fileName = sprintf("租户项目授权详细_%s.xls", \Yii::$app->user->identity->userguid);

        // 文件名称
        if (!is_dir(\Yii::getAlias("@mybackend/web/xls/"))) {
            $mkResult = mkdir(\Yii::getAlias("@mybackend/web/xls/"), 0755);
            $this->_errorLog->log(date('Y-m-d H:i:s') . ";time=" . time() . ";mkResult=" . $mkResult . ";LINE=" . __LINE__);
        }

        $objWriter->save('xls/' . $fileName); //脚本方式运行，保存在当前目录
        $filepath = \Yii::getAlias("@mybackend/web/xls/") . $fileName;
        $key = $fileName;
        OSS::upload($key, $filepath);
        $ossUrl = OSS::getUrl($key);
        return $ossUrl;
    }

    /**
     * 获取交付信息供导出
     * @param $orgcode
     * @param $projectId
     * @return array|bool
     * @throws \yii\base\InvalidConfigException
     */
    private function getExportContact($orgcode, $projectId)
    {
        if (empty($orgcode) || empty($projectId)) {
            return false;
        }
        /* @var $orgService MyscrmOrganizationRepository */
        $orgService = \Yii::createObject(MyscrmOrganizationRepository::className());
        $orgInfo = $orgService->getOrgByUniqueName($orgcode);
        if (empty($orgInfo)) {
            return false;
        }
        $orgId = $orgInfo->Id;
        /* @var $projectService ProjectService */
        $projectService = \Yii::createObject(ProjectService::className());
        $contactInfo = $projectService->getDeliverInfo($orgId, $projectId);
        $result = [
            "contractCustomManager" => "",
            "contractCustomManagerPhone" => "",
            "contractDeliver" => "",
            "contractDeliverPhone" => ""
        ];

        if (!empty($contactInfo["custom_manage"])) {
            foreach ($contactInfo["custom_manage"] as $info) {
                $result["contractCustomManager"] .= "," . $info["name"];
                $result["contractCustomManagerPhone"] .= "," . $info["mobile"];
            }
        }
        $result["contractCustomManager"] = ltrim($result["contractCustomManager"], ",");
        $result["contractCustomManagerPhone"] = ltrim($result["contractCustomManagerPhone"], ",");
        if (!empty($contactInfo["deliver"])) {
            foreach ($contactInfo["deliver"] as $info) {
                $result["contractDeliver"] .= "," . $info["name"];
                $result["contractDeliverPhone"] .= "," . $info["mobile"];
            }
        }
        $result["contractDeliver"] = ltrim($result["contractDeliver"], ",");
        $result["contractDeliverPhone"] = ltrim($result["contractDeliverPhone"], ",");
        return $result;
    }

    /**
     * 获取当前活动表格
     * @param \PHPExcel $objPHPExcel
     * @return \PHPExcel_Worksheet
     */
    private function __getActiveSheet($objPHPExcel)
    {
        if (self::$sheetIndex == 0) {
            $objPHPExcel->setActiveSheetIndex(self::$sheetIndex);
            $activeSheet = $objPHPExcel->getActiveSheet();
        } else {
            $activeSheet = $objPHPExcel->createSheet(self::$sheetIndex);
        }
        self::$sheetIndex++;
        return $activeSheet;
    }

    /**
     * 获取交付信息变更记录
     * @param $orgId
     * @return null|MyscrmDeliveryChangeLogEntity
     */
    public function getDeliveryChangeLog($orgId)
    {
        return $this->_organizationRepository->getDeliveryChangeLog($orgId);
    }

    /**
     * 设置合同过期邮件提醒
     * @param $organizationId
     * @param $contractId
     * @param $isStop
     * @param $reason
     * @param $isXq
     * @param $details
     * @param $renewStatus
     * @param $noRenewReason
     * @return bool
     */
    public function addRemind($organizationId, $contractId, $isStop, $reason, $isXq, $details, $renewStatus, $noRenewReason)
    {

        if (!$organizationId) {
            return $this->setAndReturn(__LINE__, '租户ID不能为空');
        }

        if (empty($details)){
            return $this->setAndReturn(__LINE__, 'details参数不能为空');
        }

        /** @var MyscrmOrganizationRemindRepository $organizationRemindRepository */
        $organizationRemindRepository = Yii::createObject(MyscrmOrganizationRemindRepository::className());

        $trans = ConfigEntity::getDb()->beginTransaction();
        try {

            $remindId = $organizationRemindRepository->addRemind($organizationId, $contractId, $isStop, $reason, $isXq);
            if ($remindId === false) {
                $trans->rollBack();
                return $this->setAndReturn(__LINE__, '插入主表出错');
            }

            if (!empty($details)) {
                $detailsResult = $this->addRemindDetails($organizationId, $remindId, $details, $renewStatus, $noRenewReason);
                if ($detailsResult === false) {
                    $trans->rollBack();
                    return $this->setAndReturn(__LINE__, '插入详细表出错');
                }
            }

            $trans->commit();
        } catch (\Exception $e) {
            $trans->rollBack();
            return false;
        }

        return true;
    }

    private function addRemindDetails($organizationId, $remindId, $details, $renewStatus, $noRenewReason)
    {
        $deleteSql = "";
        $insertSql = "";
        foreach ($details as $remindDetails){
            $projectId = $remindDetails->projectId;

            foreach ($remindDetails->apps as $app) {
                $appCode = $app->appCode;
                $isXq = $app->isXq;
                $contractId = $app->contractId;

                if ($isXq == 1) {
                    $renewStatusTmp = $renewStatus;
                    $noRenewReasonTmp = 0;
                } else {
                    $renewStatusTmp = 0;
                    $noRenewReasonTmp = $noRenewReason;
                }

                //同一个合同下的同一个项目+应用只能操作一遍
                $deleteSql .= sprintf("delete from `myscrm_organization_remind_details` where `projectId`='%s' and `contractId`='%s' and `appCode`='%s' ;",
                    $projectId,
                    $contractId,
                    $appCode
                );

                $insertSql .= sprintf("insert into `myscrm_organization_remind_details` (`detailsId`, `CreatedOn`, `ModifiedOn`, `ModifiedBy`, `CreatedBy`, `organizationId`, `projectId`, `contractId`, `appCode`, `isXq`, `renewStatus`, `noRenewReason`, `remindId`) values (%s,%s,%s,'%s','%s','%s','%s','%s','%s','%s','%s','%s','%s'); ",
                    'UUID()',
                    'NOW()',
                    'NOW()',
                    Yii::$app->user->identity->userguid,
                    Yii::$app->user->identity->userguid,
                    $organizationId,
                    $projectId,
                    $contractId,
                    $appCode,
                    $isXq,
                    $renewStatusTmp,
                    $noRenewReasonTmp,
                    $remindId
                );
            }

        }

        if($insertSql != ""){
            try{
                ConfigEntity::getDb()->createCommand($deleteSql . $insertSql)->execute();
            }catch (\Exception $e){
                return $this->setAndReturn(__LINE__, $e->getMessage());
            }
        }

        return true;
    }

    /**
     * 更新合同过期邮件提醒
     * @param $organizationId
     * @param $remindId
     * @param $isStop
     * @param $reason
     * @param $isXq
     * @param $renewStatus
     * @param $noRenewReason
     * @param $details
     * @return bool
     */
    public function updateRemind($organizationId, $remindId, $isStop, $reason, $isXq, $renewStatus, $noRenewReason, $details)
    {
        if (!$remindId){
            return $this->setAndReturn(__LINE__, '续签ID不能为空');
        }

        if (empty($details)){
            return $this->setAndReturn(__LINE__, 'details参数不能为空');
        }
        /** @var MyscrmOrganizationRemindRepository $organizationRemindRepository */
        $organizationRemindRepository = Yii::createObject(MyscrmOrganizationRemindRepository::className());

        $trans = ConfigEntity::getDb()->beginTransaction();
        try {
            $updateResult = $organizationRemindRepository->updateRemind($remindId, $isStop, $reason, $isXq);
            if ($updateResult === false) {
                $trans->rollBack();
                return $this->setAndReturn(__LINE__, '更新主表出错');
            }

            if (!empty($details)) {
                $detailsResult = $this->addRemindDetails($organizationId, $remindId, $details, $renewStatus, $noRenewReason);
                if ($detailsResult === false) {
                    $trans->rollBack();
                    return $this->setAndReturn(__LINE__, '插入详细表出错：' . $this->getErrMsg());
                }
            }

            $trans->commit();
        }catch (\Exception $e){
            $trans->rollBack();
            return $this->setAndReturn(__LINE__, '未知异常：' . $e->getMessage());
        }

        return true;
    }

    /**
     * 获取合同过期邮件提醒
     * @param $organizationId
     * @param $projectId
     * @param $appCode
     * @return array|null|\yii\db\ActiveRecord
     */
    public function getRemind($organizationId, $projectId, $appCode)
    {
        /** @var MyscrmOrganizationRemindRepository $organizationRemindRepository */
        $organizationRemindRepository = Yii::createObject(MyscrmOrganizationRemindRepository::className());

        //根据项目ID、应用Code查找最后一次续签汇报的remindId，再根据remindId查找列表
        $detailsTmp = $organizationRemindRepository->getRemindDetailsByProjectApp($projectId, $appCode);

        $remindInfo = $organizationRemindRepository->getRemindByRemindId($detailsTmp['remindId']);

        if (empty($remindInfo)) {
            return [];
        }

        $orgInfo = $this->getOrgById($organizationId);

        /** @var MyscrmContractProjectRepository $contractProjectRepository */
        $contractProjectRepository = Yii::createObject(MyscrmContractProjectRepository::className());
        $projectList = $contractProjectRepository->getProjectByOrgCode($orgInfo->UniqueName);
        foreach ($projectList as $project) {
            $projectArr[$project['ProjId']] = $project['ProjName'];
        }

        /** @var MyscrmApplicationService $applicationService */
        $applicationService = Yii::createObject(MyscrmApplicationService::className());
        $applicationList = $applicationService->getAllApplication();
        foreach ($applicationList as $app) {
            $applicationArr[$app->UniqueName] = $app->FriendlyName;
        }

        $remindDetails = $organizationRemindRepository->getRemindDetailsByRemindId($detailsTmp['remindId']);

        $projectIdAppCodeArr = [];
        foreach ($remindDetails as $detail) {
            $tmp = [
                'projectId' => $detail['projectId'],
                'appCode' => $detail['appCode'],
            ];
            $projectIdAppCodeArr[] = $tmp;
        }

        $checkInfo = MyscrmOrganizationRemindRepository::checkProjectAppStatus($orgInfo->UniqueName, $projectIdAppCodeArr);

        //续签节点和不续签原因从详细表中取
        $renewStatus = 0;
        $noRenewReason = 0;
        $detailsGroupBy = [];
        foreach ($remindDetails as $remindDetail) {
            $projectId = $remindDetail['projectId'];
            $projectName = isset($projectArr[$projectId]) ? $projectArr[$projectId] : '';

            $appCode = $remindDetail['appCode'];
            $appName = isset($applicationArr[$appCode]) ? $applicationArr[$appCode] : '';

            if (!isset($projectGroupBy[$projectId])) {
                $projectGroupBy[$projectId] = [
                    'projectId' => $projectId,
                    'projectName' => $projectName,
                ];
            }

            $isDisable = isset($checkInfo[$projectId][$appCode]['disable']) ? $checkInfo[$projectId][$appCode]['disable'] : 0;
            $newestContractId = isset($checkInfo[$projectId][$appCode]['contractId']) ? $checkInfo[$projectId][$appCode]['contractId'] : '';

            $apps = [
                'appCode' => $appCode,
                'appName' => $appName,
                'isXq' => $remindDetail['isXq'],
                'contractId' => $remindDetail['contractId'],
                'isCanUpdate' => 1,//是否修改续签汇报状态
                'isDisable' => $isDisable,//是否修改续签汇报状态
                'newestContractId' => $newestContractId,//是否修改续签汇报状态
            ];

            if ($isDisable == 1 || $newestContractId != $remindDetail['contractId']) {
                $apps['isCanUpdate'] = 0;
            }

            $projectGroupBy[$projectId]['apps'][] = $apps;
            $detailsGroupBy = $projectGroupBy;

            if (!empty($remindDetail['renewStatus'])) {
                $renewStatus = $remindDetail['renewStatus'];
            }
            if (!empty($remindDetail['noRenewReason'])) {
                $noRenewReason = $remindDetail['noRenewReason'];
            }
        }
        $remindInfo['details'] = array_values($detailsGroupBy);
        $remindInfo['renewStatus'] = $renewStatus;
        $remindInfo['noRenewReason'] = $noRenewReason;

        return $remindInfo;
    }

    /**
     * 获取合同过期邮件提醒详细信息
     * @param $organizationId
     * @param $contractId
     * @param array $projectIdAndAppCode
     * @return bool|array|null|\yii\db\ActiveRecord
     */
    public function getRemindDetails($organizationId, $contractId, $projectIdAndAppCode)
    {
        if(empty($projectIdAndAppCode)){
            return $this->setAndReturn(__LINE__, '要显示的项目和应用不能为空');
        }

        $orgInfo = $this->getOrgById($organizationId);

        /** @var MyscrmContractProjectRepository $contractProjectRepository */
        $contractProjectRepository = Yii::createObject(MyscrmContractProjectRepository::className());
        $contractProjectList = $contractProjectRepository->getAllByOrgCodesAndContractId($contractId, $orgInfo->UniqueName);

        $authProjects = $this->getAllAuthProject('', '', '', '', $organizationId);
        $authProjectIds = ArrayHelper::getColumn($authProjects, 'ProjId');

        //要显示的项目ID
        $showProjectIds = [];
        $filterArr = [];
        if(!empty($projectIdAndAppCode)){
            foreach ($projectIdAndAppCode as $item){
                $filterArr[$item['projectId']][] = $item['appCode'];
                $showProjectIds[] = $item['projectId'];
            }
        }

        $projectGroupBy = [];
        foreach ($contractProjectList as $contractProject) {

            $projectId = $contractProject['ProjId'];
            $projectName = $contractProject['ProjName'];

            if(!empty($showProjectIds) && !in_array($projectId, $showProjectIds)){
                continue;
            }

            //排除权限外的
            if (!in_array($projectId, $authProjectIds)) {
                continue;
            }

            $appCode = $contractProject['applicationCode'];
            $appName = $contractProject['applicationName'];

            if (!isset($projectGroupBy[$projectId])) {
                $projectGroupBy[$projectId] = [
                    'projectId' => $projectId,
                    'projectName' => $projectName,
                ];
            }

            $apps = [
                'appCode' => $appCode,
                'appName' => $appName,
            ];

            if(!empty($showProjectIds) && !in_array($appCode, $filterArr[$projectId])) {
                continue;
            }

            $projectGroupBy[$projectId]['apps'][] = $apps;
        }


        return array_values($projectGroupBy);
    }

    public function setBq($orgId)
    {
        return $this->_organizationRepository->setBq($orgId);
    }

    public function unsetBq($orgId)
    {
        return $this->_organizationRepository->unsetBq($orgId);
    }

    /**
     * 更新测试租户标记状态
     * @param $orgCode
     * @param $updateType
     * @return bool|void
     */
    public function updateTestOrg($orgCode, $updateType)
    {
        if(empty($orgCode)){
            return self::setAndReturn(__LINE__, '租户Code不能为空');
        }

        if ($updateType == 'add'){
            return $this->_organizationRepository->addTestOrganization($orgCode);
        }else {
            return $this->_organizationRepository->deleteTestOrganization($orgCode);
        }
    }

    /**
     * @param $orgId
     */
    private function saveDeleteData($orgId)
    {
        $sql = "
                INSERT INTO `data_delete_pool` 
                (`id`, `table_name`, `primary_key_name`, `primary_key_value`) VALUES 
                (:id, :table_name, :primary_key_name, :primary_key_value); 
            ";

        ConfigEntity::getDb()->createCommand($sql, [
            ':id'                => StringHelper::uuid(),
            ':table_name'        => 'myscrm_organization',
            ':primary_key_name'  => 'Id',
            ':primary_key_value' => $orgId,
        ])->execute();
    }
    public function getOrgsListByKeyword($keyword,$page,$pageSize,$fields="",$returnTatalCount=true)
    {
        return $this->_organizationRepository->getOrgsListByKeyword($keyword,$page,$pageSize,$fields,$returnTatalCount);
    }
    public function getOrgsListByCodes($orgCodes,$fields="")
    {
        return $this->_organizationRepository->getOrgsListByCodes($orgCodes,$fields);

    }
}